Re: access 2003

Goeff: Thanks for responding

Declarations set a module:
Dim frm As Form
Dim ctl As Control
Dim db As Database
Dim rs As Recordset
Dim I As Integer


1.) The zfGetDB() was created at a public function see below and it is
stored in a module in the program database.


Public Function zfGetDB() As Database
Static dbe As Database
Dim strName As String
On Error Resume Next
strName = dbe.NAME
If Err.Number <> 0 Then
Set dbe = DBEngine(0)(0)
End If
Set zfGetDB = dbe
End Function

2.) Further explaination for two lines:

'qd![Forms!frmInvoiceSelect!ChooseCust] = Me!ChooseCust
'qd![Forms!frmInvoiceSelect!ChooseJob] = Me!ChooseJob

This sets the query definitions for choosing data to create an invoice using
the cmdInvoice combo box located on the footer of the form

This works fine in access 97 the problem is with access 2003 and I was
wondering if anything changed when using parameter queries, etc to synchroize
data between a forms query record and nonbound combo boxes and text boxes.

I will try to explain how the form works.

The form frmInvoiceSelect has 3 parts:
Form Header
Detail
Form Footer

The form header has 2 combo boxes (nonbound) their job is to retrieve
information for ... Customer
Job
each combo box has a query as record source using the ROW SOURCE and ROW
SOURCE TYPE.

Customer combo box
Name: ChooseCust
ROW SOURCE: qlbCustomerOpenInvoice (is a query). This query selects
customer name and conditions: work date Is Not Null and Inovice # is Not Null
using fields with these conditions.
ROW SOURCE TYPE: Table/Query
Event on combo box: Private Sub ChooseCust_AfterUpdate()
Prive Sub ChooseJob_AfterUpdate()
On Error Resume NExt
Me.Filter On = False
Me.Requery
End Sub
see the code behind the form in earlier transmisson.

Job Combo Box
Name: Choose Job
ROW SOURCE: qlbJobByCustomer_frmInvoice
Conditions:
field: wrkdt_comp (date work completed) Criteria: IS NOT NULL
field: wrkky_invno (invoice number) Criteria: IS NULL OR 0 or -1
field:jobKy_CusNm (Customer name from Job table) Criteria: Like
nz([forms]![frmInvoiceSelect]![ChooseCust],"*")
Event on Combo Box:
Private Sub ChooseJob_After upDate()
On Error Resume Next
Me.FilterOn = False
Me.Reqery
End Sub

There are 6 lables each has an event On Click and this code is inserted not
written in a module: =zfSortLabel("tktno,"[Screen].[ActiveForm]) acess 97
places the brackets around Screen and Active Form but after converting over
to 2003 the brackets are removed. I believe this code is to sort the data in
these lables and do not believe it synchorizes the form.

Command Button called cmdSelect
with 1 event (On Click)
Prive Sub cmdSelect_Click()
Dim rs As Recordset
Set rs = Me.RecordsetClone
With rs
..MoveFirst
Do Until . EOF
.Edit
!wrkky_invno = Not !wrkky_invno
..Update
Loop
End With
rs.Close
Set rs = Nothing
End Sub
see the code that goes with the form. This command button (when selected)
tells the form to select all work orders listed in the the detail section of
this form.

Detail Secton:
6 fields - record source is the forms record source called
qryworkorders_uninvoiced.

Note: the form frmInvoiceSelect allows filters and is a continuous form.
it has 2 events:
On Open:
Private Sub Form_Open(Cancel As Integer)
Me.Fitler = "[wrkid]=0"
Me.FitlerOn = Ture
End sub

On Close:
Private Sub Form_Close()
'Remove ay wrkky_invno with a true in them before closing
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryWorkOrderDeSelect_qup"
DoCmd.SetWarnings True
End Sub

qryWorkOrderDeSelect_qup is an update query and using field: wrkky_invno,
update to: 0
Criteris: -1

Check Box:
Name: wrkky_invno (one box appears for each work order listed)
Control: wrkky_invno which is a field
this field is a part of the form query source (qryWorkOrders_uninvoiced) and
is selected for each work order you want to invoice.

Form Footer:

has 3 combo boxes
1.) cmdprebilling
event: On Click,
Private Sub cmdPrebilling_Click ()
Dim strCriteria As String
strCriteria = "jobky_cusnm = "jobky_cusnm = ' " &Me!ChooseCust & " ' " & ("
AND jobid = ' " +Me!ChooseJob + " ' ")
DoCmd.OpenReport "rpePreBilling", acViewPreview, , strCriteria
End Sub
2.) cmdInvoice On Click, Private Sub cmdInvoice_Click ()
3.) cmdClose, On Click, Private Sub cmdClose_click()

If you select the cmdPrebilling it produces the records and from that
represents the customer and job you have selected on the frmInvoiceSelect
(form header) even thought the work orders or records are not showing on the
detail section of frmInvoiceSelect. This tells me that the detail section
and the from header are not synchorized.

You can not select the check box that is associated with each work order
until they actual appear in the detail section.

Perhaps the provides more clues to help determine why the form is not
synchrorized









"Geoff" wrote:

Although I don't understand how your form and code are working, one thing
I've noticed is that it seems you should be using a parameter query to
create invoices. Here's a possible revision to your code, with explanation.
(I am using Access 2002.)

Regards
Geoff


Private Sub cmdInvoice_Click()

Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strSQL As String

DoCmd.RunCommand acCmdSaveRecord

'Make sure they have at least one workorder selected

' I'm not sure what the zfGetDB() function is:
'Set db = zfGetDB()
' So instead:
Set db = CurrentDb()

' Your temporary query was:
'Set qd = db.CreateQueryDef("", "SELECT wrkky_invno FROM " _
& "qryWorkOrders_uninvoiced WHERE wrkky_invno = true;")

' However, I don't understand the next two lines:
'qd![Forms!frmInvoiceSelect!ChooseCust] = Me!ChooseCust
'qd![Forms!frmInvoiceSelect!ChooseJob] = Me!ChooseJob


' Therefore, I've made the following assumption and
' re-written the query. I assume you're trying to restrict
' the records returned by the query in three ways:
' 1. Work Orders that are uninvoiced...
' 2. ...for the customer currently in the form...
' 3. ...for the job currently in the form.
' If that assumption is correct, then it seems you need a
' parameter query, which allows you to set the customer
' and job numbers in this code.

' I've assumed that your query needs the following parameters:
' Parameter 1 = Customer_ID number (long integer).
' Parameter 2 = Job_ID number (long integer).
' The parameters are defined in the parameters clause
' at the beginning of the SQL statement.
' The parameters are also used in the WHERE clause.
' The parameters are given values later in this code.
'
' SQL string for parameter query:
strSQL = "PARAMETERS [Customer_ID] Long, [Job_ID] Long;" _
& vbNewLine _
& "SELECT qryWorkOrders_uninvoiced.wrkky_invno" _
& "& vbNewLine" _
& "FROM qryWorkOrders_uninvoiced" & vbNewLine _
& "WHERE (((qryWorkOrders_uninvoiced.wrkky_invno)" _
& "=True) " _
& "AND ((qryWorkOrders_uninvoiced.Customer_ID)" _
& "=[Customer_ID]) " _
& "AND ((qryWorkOrders_uninvoiced.Job_ID)=[Job_ID]));"

' Incidentally, if the above looks complicated, you can easily
' create the SQL statement for a parameter query at the
' user-interface:
' 1. Open the query in design view.
' 2. Enter a criterion using square brackets, eg the
' criterion for the Customer_ID field would be [Customer_ID].
' 3. Open the Query menu, select Parameters and in the
' parameters dialog, enter [Customer_ID] in column 1 and Long
' in column 2.
' 4. Use the View menu to switch to SQL view.
' 5. Copy the SQL statement and paste it in here. The paste
' operation can be simplified by using the VBA String Editor
' Add-In (on the Add-Ins menu).

' Create temporary query:
Set qd = db.CreateQueryDef("", strSQL)

' Give the parameters values from this form:
qd.Parameters![Customer_ID] = CLng(Me.ChooseCust)
qd.Parameters![Job_ID] = CLng(Me.ChooseJob)

' Open recordset:
Set rs = qd.OpenRecordset(dbOpenSnapshot)

' Before testing the RecordCount property, you should move
' to the last record to ensure you get an accurate record
' count. However, this might be slow with large recordsets,
' so instead, you can examine the BOF and EOF properties
' (see below).
'If rs.RecordCount = 0 Then

' If the Beginning-of-File and End-of-File properties
' are both TRUE, then the recordset contains no records:
If rs.BOF And rs.EOF Then
MsgBox "You have not selected any Work Orders to invoice.", _
vbOKOnly, "No Selection"
Else
DoCmd.OpenForm "frmInvoice", , , , , , Me!ChooseJob
End If

If Not rs Is Nothing Then rs.Close
Set rs = Nothing
Set db = Nothing

End Sub



"doglover" <doglover@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:0A0934D7-5C4E-48B2-9892-1711BCE14AC1@xxxxxxxxxxxxxxxx
This is really strange sometimes the form and the text boxes synchronize
ok
and other times they do not. Here is the code behind the form:
Option Compare Database 'Use database order for string comparisons
Option Explicit
Dim X As Integer
Private Sub ChooseCust_AfterUpdate()
On Error Resume Next
Me!ChooseJob = Null
Me!ChooseJob.Requery
Me.Requery

End Sub

Private Sub ChooseJob_AfterUpdate()
On Error Resume Next
Me.FilterOn = False
Me.Requery

End Sub
Private Sub cmdClose_Click()
DoCmd.Close
End Sub

Private Sub cmdInvoice_Click()
DoCmd.RunCommand acCmdSaveRecord
'Make sure they have at least one workorder selected
Dim db As Database
Dim qd As QueryDef
Dim rs As Recordset
Set db = zfGetDB()
Set qd = db.CreateQueryDef("", "SELECT wrkky_invno FROM
qryWorkOrders_uninvoiced WHERE wrkky_invno = true;")
qd![Forms!frmInvoiceSelect!ChooseCust] = Me!ChooseCust
qd![Forms!frmInvoiceSelect!ChooseJob] = Me!ChooseJob
Set rs = qd.OpenRecordset(dbOpenSnapshot)

If rs.RecordCount = 0 Then
MsgBox "You have not selected any Work Orders to invoice.", vbOKOnly,
"No Selection"
Else
DoCmd.OpenForm "frmInvoice", , , , , , Me!ChooseJob
End If
rs.Close
Set rs = Nothing
Set db = Nothing

End Sub

Private Sub cmdPrebilling_Click()
Dim strCriteria As String
strCriteria = "jobky_cusnm = '" & Me!ChooseCust & "'" & (" AND jobid = '"
+
Me!ChooseJob + "'")
DoCmd.OpenReport "rptPreBilling", acViewPreview, , strCriteria

End Sub

Private Sub cmdSelect_Click()
Dim rs As Recordset
Set rs = Me.RecordsetClone
With rs
.MoveFirst
Do Until .EOF
.Edit
!wrkky_invno = Not !wrkky_invno
.Update
.MoveNext
Loop
End With
rs.Close
Set rs = Nothing
End Sub

Private Sub Form_Close()
'Remove any wrkky_invno with a true in them before closing
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryWorkOrderDeSelect_qup"
DoCmd.SetWarnings True
End Sub

Private Sub Form_Open(Cancel As Integer)
Me.Filter = "[wrkid]=0"
Me.FilterOn = True

End Sub


Private Sub wrkid_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmWorkOrder", , , , , , "wrk" & wrkid
End Sub

I look forward to hearing from someone that can help.
Thanks,
Doglover


"doglover" wrote:

I am converting from access 97 by importing tables, queries, forms,
reports,
macro, modules and this process is complete. I am troubleshooting one
form
that uses queries, code, etc to create an invoice. I am having trouble
synchronizing the form's query resource with text boxes and labels. The
following code in a label "On Click"
=zfSortLabel("tktno",[Screen].[ActiveForm]). I type the code in on the
"on
Click" event and the form text boxes synchronizes fine but if I
completely
close the form and reopen then the brackets around Screen have gone and
the
form does not synchronize. Obviously, it will not work with out the [].
Did
something change from Access 97 to Access 2003? Thanks for your help.
Doglover



.