access 2003

[doglover]

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

 

[doglover]

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

I am looking further and it appears that the for some reason the text boxes
and the forms underlying query resource are not synchronizing. The form
worked fine when using acces 97 but after converting it over to access 2003
that is the problem. Hopefully, someone can help me. I can supply the
complete code and any other details needed.
Thanks, Doglover

 

[doglover]

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

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

 

[Geoff]

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

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

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

 

[doglover]

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

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

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

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

 

[Geoff]

I'm beginning to understand how your form is working. The following is my
understanding (but please make corrections if necessary):

1. The user selects a customer in the "ChooseCust" combo box in the
header of the form. The After_Update event of the "ChooseCust" combo box
requeries the form. The "ChooseJob" combo box now lists only jobs for the
selected customer.

2. The user selects a job in the "ChooseJob" combo box in the header of
the form. The After_Update event of the "ChooseJob" combo box requeries the
form. The detail section should now show the uninvoiced work orders for the
selected job and for the selected customer, but the detail section does not
show these records.

3. The user should then be able to select individual work orders in the
detail section. The selected work orders are then supposed to appear on the
invoice, etc.

It seems that the query acting as the record source for the form's detail
section (qryworkorders_uninvoiced) should contain two criteria, one
criterion for the "jobky_cusnm" field and the other criterion for the
"jobid" field. These criteria should point back to the frmInvoiceSelect
form. In other words, if you open the query "qryworkorders_uninvoiced" in
design view:

(a) the criteria line for the "jobky_cusnm" field should be
[Forms]![frmInvoiceSelect]![ChooseCust] and

(b) the criteria line for the "jobid" field should be
[Forms]![frmInvoiceSelect]![ChooseJob].

If you have omitted these criteria from the query, then the detail section
will not show the correct records.

If you believe that the criteria for the form's record source are inserted
by the following two code lines:

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

then I do not believe that is right: For "qd" only refers to a temporary
query, not the query acting as the record source for the form's detail
section.

I know I have not covered the issue you mention about:
=zfSortLabel("tktno",[Screen].[ActiveForm])

Perhaps you could take a look at qryworkorders_uninvoiced first.

Regards
Geoff

 

[Geoff]

Postscript:

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)

I still do not understand how the above code works: for -

1. The SELECT statement is not preceded by a PARAMETERS clause.

2. The lines beginning "qd!" are not followed by "Parameters", eg
qd!Parameters![jobky_cusnm] = Me!ChooseCust

Regards
Geoff

 

[doglover]

Geoff:

I just checked the query that is the record source for the frmInvoiceSelect
and it has the parameters you indicated in you message. If I understand
correctly these parameters synchronize the combo boxes on the header of the
form and the detail of the form also by using the lablel or name of the combo
boxes which is either ChooseCust or ChooseJob.


I was mistaken when I indicated the
'qd![Forms!frmInvoiceSelect!ChooseCust]=Me!ChooseCust and the same code
except for Job in place of Choose this query creates the PreBilling report
and is executed when you click on the cmdPreBilling (command button) on the
footer of the form.

The combo boxes on the frmInvoiceSelect (header section) seem to be working
ok because they are retrieving the customer name and job name it is the
detail section of the form that does not retrieve any information or details
for its fields.

If you go to the query that is the record source for the frmInvoiceSelect
and remove the 2 parameters then the query retrieves all records that fit the
other criteria that relates to work orders that have a completed work date
and where the invoice is null, 0 or -1.

This form worked fine in access 97 and it actually worked once in access
2003 as created 1 prebilling form and 1 invoice but after that I am not sure
what has happened.

This is a very large database but I could removed all of the data except for
a few items or just enought that the forms will work and email.

I am trying to clean up this database as there have been several developers
working on it over the years.

In short the code that retrieves the information to build the prebilling
report is working. The problem is with the frmInvoiceSelect and appears to
be the query source for the form and the parameters. The tables seem to be
ok as they are linked. I think the =zf codes in the lables are for sorting
the data but I could be wrong.

I really appreciate your help. I have the databases loaded on my laptop but
I have a feeling your office is far away.

doglover

Postscript:

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)

I still do not understand how the above code works: for -

1. The SELECT statement is not preceded by a PARAMETERS clause.

2. The lines beginning "qd!" are not followed by "Parameters", eg
qd!Parameters![jobky_cusnm] = Me!ChooseCust

Regards
Geoff

 

[doglover]

Geoff:

Code copied from the command button cmdInvoice) which uses on click and
creates the invoice when executed not the prebilling report.

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

Code from the PreBilling command button:
Private Sub cmdPrebilling_Click()
Dim strCriteria As String
strCriteria = "jobky_cusnm = '" & Me!ChooseCust & "'" & (" AND jobid = '" +
Me!ChooseJob + "'")
DoCmd.OpenReport "rptPreBilling", acViewPreview, , strCriteria

End Sub

Thanks again for your help.

Regards,
doglover

Postscript:

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)

I still do not understand how the above code works: for -

1. The SELECT statement is not preceded by a PARAMETERS clause.

2. The lines beginning "qd!" are not followed by "Parameters", eg
qd!Parameters![jobky_cusnm] = Me!ChooseCust

Regards
Geoff

 

[Geoff]

See my comments following each of your paragraphs below.

--------------------------------------------------------------

I just checked the query that is the record source for the
frmInvoiceSelect and it has the parameters you indicated in
you message. If I understand correctly these parameters
synchronize the combo boxes on the header of the
form and the detail of the form also by using the lablel or name
of the combo boxes which is either ChooseCust or ChooseJob.

Yes, that seems right.

--------------------------------------------------------------

I was mistaken when I indicated the
'qd![Forms!frmInvoiceSelect!ChooseCust]=Me!ChooseCust and the same code
except for Job in place of Choose this query creates the PreBilling report
and is executed when you click on the cmdPreBilling (command button) on
the
footer of the form.

OK. Whichever report the above code line helps create doesn't alter the fact
that I do not understand how this code works. Of course, I accept that it IS
working (as you say so), but it doesn't make sense to me at this stage.

--------------------------------------------------------------

The combo boxes on the frmInvoiceSelect (header section) seem to be
working ok because they are retrieving the customer name and job name
it is the detail section of the form that does not retrieve any
information
or details for its fields.

If you go to the query that is the record source for the frmInvoiceSelect
and remove the 2 parameters then the query retrieves all records that fit
the other criteria that relates to work orders that have a completed work
date and where the invoice is null, 0 or -1.

The above two paragraphs imply that there is something wrong with one or
both criteria values that are coming from the combo boxes into the query.
Clearly, you have demonstrated that the query is otherwise working because,
when you remove the criteria for the Customer and Job fields, the query
returns all records that meet the other remaining criteria. (Well done!)

I suggest the way forward is to run some tests on exactly what the combo
boxes are returning to the query to ensure that the query is receiving valid
values. Here are some suggestions:

1. First, double-check that the criteria are correct in the query; ie
that the criterion for the Customer field definitely points to the
ChooseCust combo box and that the criterion for the Job field definitely
points to the ChooseJob combo box. Then...

2. In the After_Update event for the ChooseCust combo box, enter the
following code:

Debug.Print "Customer:", Me.ChooseCust.Value

3. In the After_Update event for the ChooseJob combo box, enter the
following code:

Debug.Print "Job:", Me.ChooseJob.Value

4. Open the form, select a customer and a job. Close the form. Switch to
the VBA editor. Open the Immediate window (CTRL-G). The Immediate window
will contain what the combo boxes are sending to the query. Are the combo
boxes sending valid criteria? For example, if the query expects long
integers for the CustomerID and JobID, did you see integers in the Immediate
window? More to the point, did you see the correct integers for the
customer you selected and the job you selected? If not, perhaps the bound
column of one or both combo boxes is wrong.

5. When finished, remove the Debug.Print statements from the After_Update
event code.

6. As an alternative approach, open the query in design view and
temporarily remove the criteria expressions for the Customer and Job; ie
remove:
[Forms]![frmInvoiceSelect]![ChooseCust]
[Forms]![frmInvoiceSelect]![ChooseJob]
Replace these expressions with valid values for a particular customer and
for a particular job. Run the query. Does it return the records you expect?
If not, why not?

--------------------------------------------------------------

This form worked fine in access 97 and it actually worked once in access
2003 as created 1 prebilling form and 1 invoice but after that I am not
sure what has happened.

It sounds like something simple changed after it worked once.

--------------------------------------------------------------

This is a very large database but I could removed all of the data except
for a few items or just enought that the forms will work and email.

We should try and solve this via the newsgroup if we can. It makes us both
think and communicate clearly, which is a good learning experience for both
of us, if a bit painful sometimes!

--------------------------------------------------------------

I am trying to clean up this database as there have been several
developers working on it over the years.

I thought as much.

--------------------------------------------------------------

In short the code that retrieves the information to build the prebilling
report is working. The problem is with the frmInvoiceSelect and appears
to be the query source for the form and the parameters. The tables seem
to
be ok as they are linked. I think the =zf codes in the lables are for
sorting the data but I could be wrong.

Usually, when a property begins with an equals sign, the property is calling
a function in a VBA module and that function returns a value to the
property. If you need to clarify what the =zf codes are doing, I suggest:

1. Open the VBA editor.
2. Open the EDIT menu and select FIND.
3. In the FIND dialog, click the "Current Project" option to ensure you
search through all modules.
4. In the Find What box, enter the =zf code you want to find.
5. Click Find Next.

You will probably find:
Function zf....()
...code here...
End Function

--------------------------------------------------------------

I have a feeling your office is far away.

I'm in London. Where are you?

Regards
Geoff

 

[doglover]

Geoff:

Results of double-checking the criteria are correct in the query that
retrieves information for the ChooseCust and ChooseJob:

Using the VBA editor:
I followed your instructions with regard to placing the code in the
After_Update event for the ChooseCust comb box and ChooseJob combo box. The
results were correct for both when using the VBA editor. IE, The ChooseCust
result was the correct customer name and the ChooseJob was the correct job id
number.

Using the query record source for the the frmInvoiceSelect :
I removed the parameter expressions and replace with valid values for each
field that had a parameter and the query results were correct.

Testing the =zf function:
The results:
Search text is not found.

Guess this means there is a function in a module somewhere and perhaps it
did not convert over when I imported the database components like a module?

You are correct this is a learning experience especially for me.

Jacksonville, Florida is home so I will not be driving my laptop to your
office.

I can not tell you how much I appreciate your help.

Regards,
doglover

See my comments following each of your paragraphs below.

--------------------------------------------------------------

I just checked the query that is the record source for the
frmInvoiceSelect and it has the parameters you indicated in
you message. If I understand correctly these parameters
synchronize the combo boxes on the header of the
form and the detail of the form also by using the lablel or name
of the combo boxes which is either ChooseCust or ChooseJob.

Yes, that seems right.

--------------------------------------------------------------

I was mistaken when I indicated the
'qd![Forms!frmInvoiceSelect!ChooseCust]=Me!ChooseCust and the same code
except for Job in place of Choose this query creates the PreBilling report
and is executed when you click on the cmdPreBilling (command button) on
the
footer of the form.

OK. Whichever report the above code line helps create doesn't alter the fact
that I do not understand how this code works. Of course, I accept that it IS
working (as you say so), but it doesn't make sense to me at this stage.

--------------------------------------------------------------

The combo boxes on the frmInvoiceSelect (header section) seem to be
working ok because they are retrieving the customer name and job name
it is the detail section of the form that does not retrieve any
information
or details for its fields.

If you go to the query that is the record source for the frmInvoiceSelect
and remove the 2 parameters then the query retrieves all records that fit
the other criteria that relates to work orders that have a completed work
date and where the invoice is null, 0 or -1.

The above two paragraphs imply that there is something wrong with one or
both criteria values that are coming from the combo boxes into the query.
Clearly, you have demonstrated that the query is otherwise working because,
when you remove the criteria for the Customer and Job fields, the query
returns all records that meet the other remaining criteria. (Well done!)

I suggest the way forward is to run some tests on exactly what the combo
boxes are returning to the query to ensure that the query is receiving valid
values. Here are some suggestions:

1. First, double-check that the criteria are correct in the query; ie
that the criterion for the Customer field definitely points to the
ChooseCust combo box and that the criterion for the Job field definitely
points to the ChooseJob combo box. Then...

2. In the After_Update event for the ChooseCust combo box, enter the
following code:

Debug.Print "Customer:", Me.ChooseCust.Value

3. In the After_Update event for the ChooseJob combo box, enter the
following code:

Debug.Print "Job:", Me.ChooseJob.Value

4. Open the form, select a customer and a job. Close the form. Switch to
the VBA editor. Open the Immediate window (CTRL-G). The Immediate window
will contain what the combo boxes are sending to the query. Are the combo
boxes sending valid criteria? For example, if the query expects long
integers for the CustomerID and JobID, did you see integers in the Immediate
window? More to the point, did you see the correct integers for the
customer you selected and the job you selected? If not, perhaps the bound
column of one or both combo boxes is wrong.

5. When finished, remove the Debug.Print statements from the After_Update
event code.

6. As an alternative approach, open the query in design view and
temporarily remove the criteria expressions for the Customer and Job; ie
remove:
[Forms]![frmInvoiceSelect]![ChooseCust]
[Forms]![frmInvoiceSelect]![ChooseJob]
Replace these expressions with valid values for a particular customer and
for a particular job. Run the query. Does it return the records you expect?
If not, why not?

--------------------------------------------------------------

This form worked fine in access 97 and it actually worked once in access
2003 as created 1 prebilling form and 1 invoice but after that I am not
sure what has happened.

It sounds like something simple changed after it worked once.

--------------------------------------------------------------

This is a very large database but I could removed all of the data except
for a few items or just enought that the forms will work and email.

We should try and solve this via the newsgroup if we can. It makes us both
think and communicate clearly, which is a good learning experience for both
of us, if a bit painful sometimes!

--------------------------------------------------------------

I am trying to clean up this database as there have been several
developers working on it over the years.

I thought as much.

--------------------------------------------------------------

In short the code that retrieves the information to build the prebilling
report is working. The problem is with the frmInvoiceSelect and appears
to be the query source for the form and the parameters. The tables seem
to
be ok as they are linked. I think the =zf codes in the lables are for
sorting the data but I could be wrong.

Usually, when a property begins with an equals sign, the property is calling
a function in a VBA module and that function returns a value to the
property. If you need to clarify what the =zf codes are doing, I suggest:

1. Open the VBA editor.
2. Open the EDIT menu and select FIND.
3. In the FIND dialog, click the "Current Project" option to ensure you
search through all modules.
4. In the Find What box, enter the =zf code you want to find.
5. Click Find Next.

You will probably find:
Function zf....()
...code here...
End Function

--------------------------------------------------------------

I have a feeling your office is far away.

I'm in London. Where are you?

Regards
Geoff

 

[Geoff]

See further comments below.

--------------------------------------------------------------

Using the VBA editor:
I followed your instructions with regard to placing the code in the
After_Update event for the ChooseCust comb box and ChooseJob combo box.
The results were correct for both when using the VBA editor. IE, The
ChooseCust result was the correct customer name and the ChooseJob was
the correct job id number.

I want to confirm some basics with you. Please type Yes or No after each
question below.

(1) Is the "jobky_cusnm" field a text field? (Yes or No?)

(2) Does the "jobky_cusnm" field contain the customer's name as text (eg
Joe Bloggs Inc)? (Yes or No?)

--------------------------------------------------------------

Using the query record source for the the frmInvoiceSelect :
I removed the parameter expressions and replace with valid values for
each > field that had a parameter and the query results were correct.

(3) Is the form "frmInvoiceSelect" the form with the two combo boxes?
(Yes or No?)

(4) Is the query "qryworkorders_uninvoiced" the record source for the
form "frmInvoiceSelect"? (Yes or No?)

Question (4) means:
(a) Open "frmInvoiceSelect" in design view
(b) Open the Properties dialog for the form (it will say "Form" in
the Titlebar of the Properties dialog).
(c) Click the Data tab.
(d) Does the "Record Source" property contain
"qryworkorders_uninvoiced"? (Yes or No?)

(5) Open the query "qryworkorders_uninvoiced" in design view and
temporarily remove the criteria expressions for the "jobky_cusnm" and
"jobid" fields and replace them with a customer name and job number that you
know will return records. Run the query and make sure that it lists
records. Make a note of the customer name and job number. Close the query
without saving.

(6) Open the form "frmInvoiceSelect". Select the same customer and job
number you used in paragraph (5) above. Do you now see records in the
detail section? (Yes or No?)

--------------------------------------------------------------

Testing the =zf function:
The results:
Search text is not found.

Guess this means there is a function in a module somewhere and perhaps it
did not convert over when I imported the database components like a
module?

I take it you searched for "=zf" (without the quotation marks).

Did you get any warnings that code would not be converted when you upgraded?
It sounds a bit odd that code would not be converted - after all you are
upgrading, not downgrading, and you're going from Access 97 to Access 2003,
not to a completely different product. I would have thought that a function
written by a previous developer would have been converted OK.

Give me an example of exactly where one of these "=zf" codes exists.

--------------------------------------------------------------

Jacksonville, Florida is home so I will not be driving my laptop to your
office.


--------------------------------------------------------------

Regards
Geoff

 

[doglover]

See further comments below.



I want to confirm some basics with you. Please type Yes or No after each
question below.

(1) Is the "jobky_cusnm" field a text field? (Yes or No?) Yes

(2) Does the "jobky_cusnm" field contain the customer's name as text (eg
Joe Bloggs Inc)? (Yes or No?) Yes



(3) Is the form "frmInvoiceSelect" the form with the two combo boxes?
(Yes or No?) Yes

(4) Is the query "qryworkorders_uninvoiced" the record source for the
form "frmInvoiceSelect"? (Yes or No?) Yes

Question (4) means:
(a) Open "frmInvoiceSelect" in design view
(b) Open the Properties dialog for the form (it will say "Form" in
the Titlebar of the Properties dialog).
(c) Click the Data tab.
(d) Does the "Record Source" property contain
"qryworkorders_uninvoiced"? (Yes or No?) Yes

(5) Open the query "qryworkorders_uninvoiced" in design view and
temporarily remove the criteria expressions for the "jobky_cusnm" and
"jobid" fields and replace them with a customer name and job number that you
know will return records. Run the query and make sure that it lists
records. Make a note of the customer name and job number. Close the query
without saving. Done

(6) Open the form "frmInvoiceSelect". Select the same customer and job
number you used in paragraph (5) above. Do you now see records in the
detail section? (Yes or No?) Yes, and I investigated further. I clicked on the command button cmdPreBilling and it produced the prebilling report that represents the data on the detail section of the frmInvoiceSelect and it produced an invoice when I clicked on the command button cmdInvoice. I verified that this work order was no longer showing up as uninvoiced.



I take it you searched for "=zf" (without the quotation marks). That is correct.

Did you get any warnings that code would not be converted when you upgraded?
It sounds a bit odd that code would not be converted - after all you are
upgrading, not downgrading, and you're going from Access 97 to Access 2003,
not to a completely different product. I would have thought that a function
written by a previous developer would have been converted OK. No warning or messages but I had to import all of the tables, queries, forms, macros, modules, etc because their database was unsecured and it would not allow the conversion. So I actually created a new database to store the program items and a seperate database to store the data files. I linked them together using the interface not code.

Give me an example of exactly where one of these "=zf" codes exists.

Form Header:
label
label name: btnktkno
=zfSortLabel("tktno,"Screen.ActiveForm)
This label is associated with the work order text box on the detail section
Note: the access 97 version had brackets around Screen and ActiveForm.
However, if you place them around this code and close the label and reopen
the brackets are gone.
This label has an associated test box on the detail sectiion of the form and
the only event is a function to open the work order by double clicking.

5 other labels on the Form Header with the same code in the "on click" event
and they all are =zfSortLabel("tktdt",Screen.ActiveForm).

On the Form Header
another label
label name: btntktfl_invoice
On Click event: =zfSortLabel("tktfl_invoice",Screen.ActiveForm)

This is used to select all of the work orders listed in the detail like a
"select all" option. I have not tested this button.

How can you test the labels on the from header to determine their
relationship with a text box on the detail section?

Could a module be hidden like you can hide a column in excel?

Regards,
doglover

 

[Geoff]

Thanks for the reply.

It's midnight here so I'm turning in for the night. I'll look at your
replies in detail tomorrow.

In the meantime, just one further thought:

I've been wondering whether your After_Update code for the ChooseCust combo
box should have the following code lines in order to update the ChooseJob
combo box (to list only the jobs for the selected customer):

' The following line to clear the ChooseJob combo box:
Me.ChooseJob.Value = ""

' The following line to requery the ChooseJob
' combo box based on the newly-selected customer
' in the ChooseCust combo box:
Me.ChooseJob.Requery

Your existing code (Me.Requery) only requeries the query acting as the
record source for the form.

Geoff

 

[doglover]

Goeff:

I will try inserting the code tomorrow. I will be traveling most of
tomorrow but back to the computer later in the afternoon. I will enter your
suggestion and see where it goes. I wonder if any of the code changed from
97 to 2003 as I noticed brackets no longer are required in 2003 in some
cases.

Tomorrow I will review the form, combo boxes, etc to look for more clues.

Again, I can not possible thank you enough for all of your help.

Regards,
doglover

 

[Geoff]

Thanks for confirming the basics.

The comments below are follow-ups as the occur to me, so are not in any
logical order.

(1) Is the "jobky_cusnm" field a text field? (Yes or No?) Yes

(2) Does the "jobky_cusnm" field contain the customer's name as text
(eg
Joe Bloggs Inc)? (Yes or No?) Yes

I got a bit jittery when I noticed that the "jobky_cusnm" field is used in
the query "qlbJobByCustomer_frmInvoice"; specifically, that the query uses
the following criterion expression for the "jobky_cusnm" field:

Like Nz([Forms]![frmInvoiceSelect]![ChooseCust],"*")

I've been wondering how the database copes with customers with identical
names. Perhaps there is a Customers table with a primary or unique index on
the customer's name field?

--------------------------------------------------------------

Yes, and I investigated further. I clicked on the command button
cmdPreBilling and it produced the prebilling report that represents
the data on the detail section of the frmInvoiceSelect and it produced
an invoice when I clicked on the command button cmdInvoice.
I verified that this work order was no longer showing up as uninvoiced.

What do we conclude from the above test? It is possible to choose specific
combinations of customer name and job number that will return records when
entered directly as criteria expressions into the query. Then, when
selecting the same customer name and job number in the form
"frmInvoiceSelect", the Detail Section does show records and the PreBilling
report works OK.

If I am right in still thinking that the Detail Section is still not showing
records when some combinations of customer name and job number are used,
then the question that now arises is why are some combinations of customer
names and job numbers showing records in the Detail Section and other
combinations not showing records? One possible answer to this question is
that the ChooseCust combo box and the ChooseJob combo box are not displaying
valid combinations of customers and job numbers. If this is the case, then
we need to focus on:

1. Does my previous post solve the problem, ie does putting the code
(Me.ChooseJob.Requery) into the After_Update event of the ChooseCust combo
box solve the problem? (In other words, does this additional code restrict
the combo boxes to valid combinations of customer and job number?)

2. If not, what else could be wrong with the criteria that restrict the
customer names or job numbers in the combo boxes? For example, could some
already-invoiced jobs be slipping in?

It seems so odd that this all worked under Access 97 but not under Access
2003.

I think your question may have hit the nail on the head!

Yes, it is possible to hide a module. The user won't see the module at the
user interface. However, you will still see the module in Project Explorer
in the VBA editor. Significantly, hidden modules in one database are not
imported into another database when you use "Get External Data" on the File
menu.

To unhide any hidden modules in the original database and import them into
the new database, follow these steps (much of which you know already):

1. Open the original database.
2. At the Database window, open the Tools menu and select Options.
3. On the View tab, in the Show section, check Hidden Objects.
4. Click Apply and OK.
5. Make a note of the unhidden modules.
6. Close the database.
7. Open the new database.
8. Use "Get External Data" and select only the modules that had been
hidden before.
9. Return to the original database and uncheck Hidden Objects.

--------------------------------------------------------------

I think you're getting closer!
Regards
Geoff

 

[Geoff]

Postscript:

To hide the modules in the new database (that were hidden in the old
database), follow these steps:

1. At the database window, right-click a module you want to hide.
2. Select Properties from the right-click menu.
2. In the Properties dialog, check the Attributes/Hidden check box.
3. Click Apply and OK.

Geoff.

Incidentally, is your first language French? Just curious...

 

[Geoff]

Correction (apologies):

Clearly, you should have a function in a standard module called
"zfSortLabel".

In a standard module, the function should appear something like this:

Public Function zfSortLabel(Argument1, Argument2)
... Code here...
End Function

Therefore, I should have advised you to search for "zfSortLabel" (not "=zf")
in the VBA editor.

Notice, there is no leading equals sign (=) when you search for
"zfSortLabel". This is because there is no leading equals sign in the
function's definition (see above - "Public Function zfSortLabel"). As you
know, you're trying to find the function to establish its existence or
absence.

In contrast, the leading equals sign is used in the OnClick event property
of the 5 labels in the form header ("btnktkno", "btntktfl_invoice", etc).
This is because the equals sign tells Access to call a function when the
OnClick event is fired. Therefore, the OnClick event property calls the
function as "=zfSortLabel(arg1, arg2). Obviously, the function does
something using the arguments that are passed to it. As you indicate, it
probably sorts something on the form. It may sort something different
depending on which label is clicked - which may send different arguments to
the function.

Sorry if this is all obvious to you - I'm just correcting an earlier
mistake.

Geoff.

 

[doglover]

Geoff:
1.)
The code behind the After_Update event on ChooseCust combo box:
Private Sub ChooseCust_AfterUpdate()
On Error Resume Next
Me!ChooseJob = Null
Me!ChooseJob.Requery
End Sub

The code behind the After_update event on the ChooseJob combo box:
Private Sub ChooseJob_AfterUpdate()
On Error Resume Next
Me.FilterOn = False
Me.Requery
End Sub

frmInvoiceSelect allows filters and has the following code:

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

End Sub


Tell me exactly where you want the code placed in the ChooseJob combo box
and the ChooseCust combo box.

I just want to make sure that I am following your instructions correctly.


2.) Could the code be missing that tells the expressions placed in form
frmInvoiceSelect and and the associated queries be missing. I assumed that
an access built in function directs the expressions to fire. I was wondering
if perhaps a built-in function could be missing or a predefined property?
Just a thought.

I am attempting to look for missing modules in the Access 97 version will
advise in a seperate post.

I am a Florida native and unfortunately I do not speak French. I am
curious, why French?

Regards,
doglover

 

[doglover]

Geoff:

So far No missing modules found in the program database or the database that
stores data.

In response to your question about the jobky_cusnm field.

table: tblCustomers
Stores customer names and information abount customers. The primary key is
CusNm which is a text field (field size 50)

jobky_CusNm
is the field in the tblJob that forms the relationship between tblCustomers
and tblJob
jobKy_CusNm is a text field (field size 50)


It seeems to be me that the expressions in the queries are not communicating
with the combo boxes on the form.

If there was a problem with the relationship then the queries would not
retrieve the correct data, right?

Thanks again for your help.

Regards,
doglover

Geoff:
1.)
The code behind the After_Update event on ChooseCust combo box:
Private Sub ChooseCust_AfterUpdate()
On Error Resume Next
Me!ChooseJob = Null
Me!ChooseJob.Requery
End Sub

The code behind the After_update event on the ChooseJob combo box:
Private Sub ChooseJob_AfterUpdate()
On Error Resume Next
Me.FilterOn = False
Me.Requery
End Sub

frmInvoiceSelect allows filters and has the following code:

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

End Sub


Tell me exactly where you want the code placed in the ChooseJob combo box
and the ChooseCust combo box.

I just want to make sure that I am following your instructions correctly.


2.) Could the code be missing that tells the expressions placed in form
frmInvoiceSelect and and the associated queries be missing. I assumed that
an access built in function directs the expressions to fire. I was wondering
if perhaps a built-in function could be missing or a predefined property?
Just a thought.

I am attempting to look for missing modules in the Access 97 version will
advise in a seperate post.

I am a Florida native and unfortunately I do not speak French. I am
curious, why French?

Regards,
doglover

Correction (apologies):

Clearly, you should have a function in a standard module called
"zfSortLabel".

In a standard module, the function should appear something like this:

Public Function zfSortLabel(Argument1, Argument2)
... Code here...
End Function

Therefore, I should have advised you to search for "zfSortLabel" (not "=zf")
in the VBA editor.

Notice, there is no leading equals sign (=) when you search for
"zfSortLabel". This is because there is no leading equals sign in the
function's definition (see above - "Public Function zfSortLabel"). As you
know, you're trying to find the function to establish its existence or
absence.

In contrast, the leading equals sign is used in the OnClick event property
of the 5 labels in the form header ("btnktkno", "btntktfl_invoice", etc).
This is because the equals sign tells Access to call a function when the
OnClick event is fired. Therefore, the OnClick event property calls the
function as "=zfSortLabel(arg1, arg2). Obviously, the function does
something using the arguments that are passed to it. As you indicate, it
probably sorts something on the form. It may sort something different
depending on which label is clicked - which may send different arguments to
the function.

Sorry if this is all obvious to you - I'm just correcting an earlier
mistake.

Geoff.