cmd button SUDDENLY doesn't work opening linked form

[Michelle Ives]

I'M GOING CRAZY! I have a form with a command button which
on click opens another form and uses as link criteria the
field [LocId]. Up until now, clicking this button when
having a null in [LocId] would still open the linked form,
but with the filter set to [LocId]=0.

Starting today, if [LocId] is null, clicking the command
button gives the following message:
"Syntax error (missing operator)in query
expression '[LocId]='."

When I went to a backup of this database from 4 days ago
and tested the form, it works fine! I haven't made any
design changes to either of the forms, the queries used as
their record sources, the tables, or relationships. I've
tried replacing the non-working forms with the ones from
the backup that work, (queries also), but no luck. I've
tried comparing everything I can think of that might cause
my problem, but can't see any differences. The database
isn't 100% secure, so there is a remote possibility some
nimrod here could have done something, but no one will
admit to anything.

I would appreciate any help on what I could be missing!!!!

 

[Media Lint]

Paste the code behind the button and we can tell you if
some nimrod did something.

Probably not, it's more likely a case of "I put air in my
tires and now the car won't start" ... obviously the cause
of the problem is the air ... you see?

 

[Bruce M. Thompson]

Response below.

I'M GOING CRAZY! I have a form with a command button which
on click opens another form and uses as link criteria the
field [LocId]. Up until now, clicking this button when
having a null in [LocId] would still open the linked form,
but with the filter set to [LocId]=0.

Starting today, if [LocId] is null, clicking the command
button gives the following message:
"Syntax error (missing operator)in query
expression '[LocId]='."

When I went to a backup of this database from 4 days ago
and tested the form, it works fine! I haven't made any
design changes to either of the forms, the queries used as
their record sources, the tables, or relationships. I've
tried replacing the non-working forms with the ones from
the backup that work, (queries also), but no luck. I've
tried comparing everything I can think of that might cause
my problem, but can't see any differences. The database
isn't 100% secure, so there is a remote possibility some
nimrod here could have done something, but no one will
admit to anything.

I would appreciate any help on what I could be missing!!!!

Have you compacted the MDB lately? Back up first (to a new location so as not to
overwrite files that contain working forms) so that you'll still have the
original structure/data if something goes wrong.

It's obvious that you still have your forms, queries and other objects in the
same MDB file with your data tables. You really should consider splitting the
data from your application to protect your data in instances such as this. See
the following page at Tony Toews' web site for more information on the value and
"hows" of splitting your application:

Splitting your Microsoft Access MDB into a front end and back end
http://www.granite.ab.ca/access/splitapp.htm

 

[Michelle Ives]

Here's the code, (which is identical to that behind the
button on the working form)

Private Sub cmdOpenJobLoc_Click()
On Error GoTo Err_cmdOpenJobLoc_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmJobLocation"
stLinkCriteria = "[LocId]=" & "'" & Me![LocId] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_cmdOpenJobLoc_Click:
Exit Sub
Err_cmdOpenJobLoc_Click:
MsgBox Err.Description
Resume Exit_cmdOpenJobLoc_Click
End Sub

Thanks for the help!

-----Original Message-----
Paste the code behind the button and we can tell you if
some nimrod did something.

Probably not, it's more likely a case of "I put air in my
tires and now the car won't start" ... obviously the cause
of the problem is the air ... you see?

-----Original Message-----
I'M GOING CRAZY! I have a form with a command button which
on click opens another form and uses as link criteria the
field [LocId]. Up until now, clicking this button when
having a null in [LocId] would still open the linked form,
but with the filter set to [LocId]=0.

Starting today, if [LocId] is null, clicking the command
button gives the following message:
"Syntax error (missing operator)in query
expression '[LocId]='."

When I went to a backup of this database from 4 days ago
and tested the form, it works fine! I haven't made any
design changes to either of the forms, the queries used as
their record sources, the tables, or relationships. I've
tried replacing the non-working forms with the ones from
the backup that work, (queries also), but no luck. I've
tried comparing everything I can think of that might cause
my problem, but can't see any differences. The database
isn't 100% secure, so there is a remote possibility some
nimrod here could have done something, but no one will
admit to anything.

I would appreciate any help on what I could be missing!!!!
.

.

 

[Bruce M. Thompson]

Private Sub cmdOpenJobLoc_Click()

On Error GoTo Err_cmdOpenJobLoc_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmJobLocation"
stLinkCriteria = "[LocId]=" & "'" & Me![LocId] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_cmdOpenJobLoc_Click:
Exit Sub
Err_cmdOpenJobLoc_Click:
MsgBox Err.Description
Resume Exit_cmdOpenJobLoc_Click
End Sub

Sounds like a problem with the Null value. Try the following modification to see
if the results are more acceptable:

'******CODE START
Private Sub cmdOpenJobLoc_Click()
On Error GoTo Err_cmdOpenJobLoc_Click

'Check for Null value before opening form
If Not IsNull(Me![LocId]) Then
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmJobLocation"
stLinkCriteria = "[LocId]=""" & Me![LocId] & """"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Else 'Location ID is Null
MsgBox "The Location ID field is empty."
Goto Exit_cmdOpenJobLoc_Click
End If

Exit_cmdOpenJobLoc_Click:
Exit Sub
Err_cmdOpenJobLoc_Click:
MsgBox Err.Description
Resume Exit_cmdOpenJobLoc_Click
End Sub
'******CODE END

 

[Bruce M. Thompson]

If Not IsNull(Me![LocId]) Then

Actually, make that:

If Not IsNull(Me![LocId]) And Not Me.NewRecord Then

 

[Guest]

Hey Bruce -
I compact and backup my databases every night. I have intended for quite a while to split them into .MDB / .MDE files, (and hopefully speed up performance) but since I've still got about 20% of the form/report design left to complete, I've been putting it off. I should probably go ahead and do it and then worry about updating everyone with a finished version later.

Thanks for the code. I had tried to fix my problem using the IsNull function, but I'm still pretty new to this (as you can probably tell) and couldn't get it to do what I needed. With your example I replaced the message box after "Else" with code to open the form in add mode so the job location can be added, which is kind of what used to happen before, and it seems to work.

THANKS A TON!!


Have you compacted the MDB lately? Back up first (to a new location so as not to
overwrite files that contain working forms) so that you'll still have the
original structure/data if something goes wrong.

It's obvious that you still have your forms, queries and other objects in the
same MDB file with your data tables. You really should consider splitting the
data from your application to protect your data in instances such as this. See
the following page at Tony Toews' web site for more information on the value and
"hows" of splitting your application:

Splitting your Microsoft Access MDB into a front end and back end
http://www.granite.ab.ca/access/splitapp.htm

 

[Guest]

I spoke too soon & didn't test opening & linking frmJobLocation when there is a value for [LocId]. I get the following error:

"The OpenForm action was canceled.
You used a method of the DoCmd object to carry out an action in Visual Basic, but then clicked Cancel in a dialog box.
For example, you used the Close method to close a changed form, then clicked Cancel in the dialog box that asks if you want to save the changes you made to the form."

Any suggestions?

 

[Bruce M. Thompson]

I spoke too soon & didn't test opening & linking frmJobLocation when there is
a value for [LocId]. I get the following error:

"The OpenForm action was canceled.
You used a method of the DoCmd object to carry out an action in Visual Basic,

but then clicked Cancel in a dialog box.

For example, you used the Close method to close a changed form, then clicked

Cancel in the dialog box that asks if you want to save the changes you made to
the form."

Any suggestions?

Of course! <g>

You can trap for that error in your calling procedure's errorhandler (where you
have implemented the "OpenForm" method:

'****EXAMPLE START
Private Sub MyProcedure()
On Error Goto MyProcedure_Err

'<Your code here>

MyProcedure_Exit:
Exit Sub

MyProcedure_Err:
If Err.Number = 2501 Then 'Operation cancelled
Resume Next
Else
MsgBox "Error: " & Err.Number & " - " & Err.Description
Resume MyProcedure_Exit

End Sub
'****EXAMPLE END

Of course, "MyProcedure" would be the name of *your* procedure.

 

[Guest]

Couldn't replay to last post.
I tried the code, and first got an error due to a missing End If, which I added. Tested clicking the button with null in [LocId] and form opens in add mode no problem. Clicked it with a value in [LocId], and nothing happened. I added a message box before the line “Resume Next†and tried again. Message box popped up, but form still won’t open.
Tried starting from scratch and adding new button to open frmJobLocation and display related record, works fine, unless [LocId] is null -back to original problem.
Since I also have a command button (STILL WORKING!) to do pretty much the same thing for frmCustomer, using [CustId] as the linked field, I compared everything I could think of to see what might be different between the two, including:
Table relationships (all three tables)
Field properties for [CustId] (primary key text field) & [LocId] (primary key autonumber) in their tables
Field properties for both lookup fields in the main table, including Indexed (yes/duplicates), Required (no), Default Value (none), Allow Zero Length (no – for [CustId])
Combo box properties for the lookup fields, checking the Bound Column just in case, Limit To List (yes)
Form properties for the two linked forms, including Data (no differences), and Event Procedures, especially On Open, On Current, On Load, On Activate (no differences)
Why does one still work, while the other won’t?!? Do I have to choose between being able to open frmJobLocation and showing related record if [LocId] has value, but not being able to add one if [LocId] is null - "Syntax error (missing operator) in query expression '[LocId]='." OR
Being able to open frmJobLocation to add record if [LocId] is null, but not opening it to display related record if [LocId] has value - “Run-time error ‘2501’: The OpenForm action was canceled. You used a method of the DoCmd object to carry out an action in Visual Basic, but then clicked Cancel in a dialog box….†WHICH I DIDN’T!!


----- Bruce M. Thompson wrote: -----

Response below.

I'M GOING CRAZY! I have a form with a command button which
on click opens another form and uses as link criteria the
field [LocId]. Up until now, clicking this button when
having a null in [LocId] would still open the linked form,
but with the filter set to [LocId]=0.

Starting today, if [LocId] is null, clicking the command

button gives the following message:
"Syntax error (missing operator)in query
expression '[LocId]='."

When I went to a backup of this database from 4 days ago

and tested the form, it works fine! I haven't made any
design changes to either of the forms, the queries used as
their record sources, the tables, or relationships. I've
tried replacing the non-working forms with the ones from
the backup that work, (queries also), but no luck. I've
tried comparing everything I can think of that might cause
my problem, but can't see any differences. The database
isn't 100% secure, so there is a remote possibility some
nimrod here could have done something, but no one will
admit to anything.

I would appreciate any help on what I could be missing!!!!

Have you compacted the MDB lately? Back up first (to a new location so as not to
overwrite files that contain working forms) so that you'll still have the
original structure/data if something goes wrong.

It's obvious that you still have your forms, queries and other objects in the
same MDB file with your data tables. You really should consider splitting the
data from your application to protect your data in instances such as this. See
the following page at Tony Toews' web site for more information on the value and
"hows" of splitting your application:

Splitting your Microsoft Access MDB into a front end and back end
http://www.granite.ab.ca/access/splitapp.htm

 

[Bruce M. Thompson]

Couldn't replay to last post.

Which post?

I tried the code,

Which code?

... and first got an error due to a missing End If, which I added.

If you're referring to the code that I provided, then you must have left it out,
because it is present in my example.

Tested clicking the button with null in [LocId] and form opens in add mode

no problem. Clicked it with a value in [LocId], and nothing happened. I added
a message box before the line "Resume Next" and tried again. Message box popped
up, but form still won't open.

And the code that doesn't work is ...

Tried starting from scratch and adding new button to open frmJobLocation and

display related record, works fine, unless [LocId] is null -back to original
problem.

Since I also have a command button (STILL WORKING!) to do pretty much the

same thing for frmCustomer, using [CustId] as the linked field, I compared
everything I could think of to see what might be different between the two,
including:

Table relationships (all three tables)
Field properties for [CustId] (primary key text field) & [LocId] (primary key autonumber) in their tables
Field properties for both lookup fields in the main table, including Indexed

(yes/duplicates), Required (no), Default Value (none), Allow Zero Length (no -
for [CustId])

How can you have a Customer ID that can be "Null" or duplicated? That wouldn't
be an ID at all! Any ID field should be unique to each record and *every* record
should have an ID, otherwise the ID field is worthless.

Combo box properties for the lookup fields, checking the Bound Column just in case, Limit To List (yes)
Form properties for the two linked forms, including Data (no differences), and

Event Procedures, especially On Open, On Current, On Load, On Activate (no
differences)

Why does one still work, while the other won't?!? Do I have to choose between

being able to open frmJobLocation and showing related record if [LocId] has
value, but not being able to add one if [LocId] is null - "Syntax error
(missing operator) in query expression '[LocId]='." OR

(??) You said above that you "tested clicking the button with null in [LocId]
and form opens in add mode no problem" and that nothing happened when [LocID]
did contain a value. Now you are saying that it's doing just the opposite (of
course your "started over", right? :-& ). I'm afraid I can't make heads or
tails out of that. Just post your code (and post it in reply to this message so
we can follow the thread).

Being able to open frmJobLocation to add record if [LocId] is null, but not

opening it to display related record if [LocId] has value - "Run-time error
'2501': The OpenForm action was canceled. You used a method of the DoCmd object
to carry out an action in Visual Basic, but then clicked Cancel in a dialog
box.." WHICH I DIDN'T!!

You haven't included any of the code you are presently using. It sounds as
though you have code in the form's "On Open" event procedure that is canceling
the open event; then, again, that may not be the problem. If there is code in
the "On Open" event procedure, include that entire procedure with the other code
that you are using to open the form (the entire procedure here, too) so that
someone can understand what you are doing.

 

[Guest]

Sorry about my confusing post. Following is applicable code from the Class Modules of the three forms in question. I have tried to address your questions with notes in the code, and hopefully it's a little clearer. In response to the following:

[CustId] and [LocId] are the primary keys in the Customer and JobLocation tables, but were also used as field names for the foreign key lookup fields in the JobDetail table. (not the best database design, but I was very inexperienced when first starting these databases) The field properties Required (No), and Indexed (Yes, Duplicates Ok) apply to the foreign key fields in JobDetail.BEGINNING OF FORM_ frmJobDetail: CLASS MODULE
Private Sub Form_Activate()
DoCmd.RunCommand acCmdRefresh
End Sub
_______________________________________________________________________________________________
Private Sub Form_Current()
If Me![Intl] = True Then
[SiteCityIntl].Visible = True
[SiteZipIntl].Visible = True
[SiteState].Visible = False
[SiteCity].Visible = False
[SiteZip].Visible = False
Else
[SiteCityIntl].Visible = False
[SiteZipIntl].Visible = False
[SiteState].Visible = True
[SiteCity].Visible = True
[SiteZip].Visible = True
End If
End Sub
_______________________________________________________________________________________________
Private Sub LocId_AfterUpdate()
DoCmd.RunCommand acCmdSaveRecord
DoCmd.SetWarnings False
DoCmd.OpenQuery "qupdJobLocDetail", acNormal, acEdit
DoCmd.SetWarnings True
DoCmd.RunCommand acCmdRefresh
If Me![Intl] = True Then
[SiteCityIntl].Visible = True
[SiteZipIntl].Visible = True
[SiteState].Visible = False
[SiteCity].Visible = False
[SiteZip].Visible = False
Else
[SiteCityIntl].Visible = False
[SiteZipIntl].Visible = False
[SiteState].Visible = True
[SiteCity].Visible = True
[SiteZip].Visible = True
End If
Forms!frmJobDetail!LocId.SetFocus
End Sub
________________________________________________________________________________________________
'This button still works -opens the customer form with linked record if [CustId] has value, opens it in add mode if [CustId] is Null
Private Sub cmdOpenCustFrm_Click()
On Error GoTo Err_cmdOpenCustFrm_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmCustomer"
stLinkCriteria = "[CustId]=" & "'" & Me![CustId] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_cmdOpenCustFrm_Click:
Exit Sub
Err_cmdOpenCustFrm_Click:
MsgBox Err.Description
Resume Exit_cmdOpenCustFrm_Click
End Sub
________________________________________________________________________________________________
'PROBLEM BUTTON -currently opens job location form in add mode if [LocId] is null, but if [LocId] has a value still won't open job location form and show related record. Until I added Bruce's error handler, resulted in following error: '"The OpenForm action was canceled. You used a method of the DoCmd object to carry out an action in Visual Basic, but then clicked Cancel in a dialog box. For example, you used the Close method to close a changed form, then clicked Cancel in the dialog box that asks if you want to save the changes you made to the form."

Private Sub cmdOpenJobLoc_Click() 'THIS STARTS CODE COPIED FROM BRUCE
On Error GoTo cmdOpenJobLoc_Click_Err
'Check for Null value before opening form
If Not IsNull(Me![LocId]) Then 'BEFORE ADDING IsNull CODING NULL [LocId] PRODUCED ERROR "Syntax error (missing operator)in query expression '[LocId]='."

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmJobLocation"
stLinkCriteria = "[LocId]=""" & Me![LocId] & """"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Else 'Location ID is Null
'FOLLOWING CODE IS MINE REPLACING BRUCE'S (MsgBox "The Location ID field is empty." GoTo Exit_cmdOpenJobLoc_Click)
DoCmd.OpenForm "frmJobLocation", , , , acAdd, acNormal
End If
'BACK TO BRUCE'S CODE
cmdOpenJobLoc_Click_Exit:
Exit Sub
cmdOpenJobLoc_Click_Err:
If Err.Number = 2501 Then 'Operation cancelled
Resume Next
Else
MsgBox "Error: " & Err.Number & " - " & Err.Description
Resume cmdOpenJobLoc_Click_Exit
End If 'THIS END IF WAS NOT IN THE CODE FROM THE POST
'http://communities2.microsoft.com/c...ding&mid=85c6ba1d-8ac2-4b62-80e3-268c665bac0c
End Sub 'END OF BRUCE'S CODE
________________________________________________________________________________________________
END OF FORM_ frmJobDetail: CLASS MODULEBEGINNING OF FORM_ frmCustomer: CLASS MODULE
________________________________________________________________________________________________
Private Sub Form_Current()
If Me![Intl] = True Then
[CustZipIntl].Visible = True
[CustMainPhoneIntl].Visible = True
[CustMainFaxIntl].Visible = True
[CustCityIntl].Visible = True
[CustSt].Visible = False
[CustCity].Visible = False
[CustZip].Visible = False
[CustMainPhone].Visible = False
[CustMainFax].Visible = False
Else
[CustZipIntl].Visible = False
[CustMainPhoneIntl].Visible = False
[CustMainFaxIntl].Visible = False
[CustCityIntl].Visible = False
[CustCity].Visible = True
[CustSt].Visible = True
[CustZip].Visible = True
[CustMainPhone].Visible = True
[CustMainFax].Visible = True
End If
End Sub
________________________________________________________________________________________________
Private Sub Form_Deactivate()
DoCmd.RunCommand acCmdSaveRecord
DoCmd.RunCommand acCmdRefresh
End Sub
________________________________________________________________________________________________
END OF FORM_ frmCustomer: CLASS MODULEBEGINNING OF FORM_ frmJobLocation: CLASS MODULE
________________________________________________________________________________________________
Private Sub Form_Current()
If Me![Intl] = True Then
[SiteZipIntl].Visible = True
[SitePhoneIntl].Visible = True
[SiteFaxIntl].Visible = True
[SiteCityIntl].Visible = True
[SiteState].Visible = False
[SiteCity].Visible = False
[SiteZip].Visible = False
[SitePhone].Visible = False
[SiteFax].Visible = False
Else
[SiteZipIntl].Visible = False
[SitePhoneIntl].Visible = False
[SiteFaxIntl].Visible = False
[SiteCityIntl].Visible = False
[SiteCity].Visible = True
[SiteState].Visible = True
[SiteZip].Visible = True
[SitePhone].Visible = True
[SiteFax].Visible = True
End If
End Sub
________________________________________________________________________________________________
Private Sub Form_Deactivate()
DoCmd.RunCommand acCmdRefresh
End Sub
________________________________________________________________________________________________
END OF FORM_ frmJobLocation: CLASS MODULE

 

[Bruce M. Thompson]

Responses interspersed below.

Sorry about my confusing post. Following is applicable code from the Class

Modules of the three forms in question. I have tried to address your questions
with notes in the code, and hopefully it's a little clearer. In response to the
following:wouldn't be an ID at all! Any ID field should be unique to each record and
*every* record should have an ID, otherwise the ID field is worthless.

[CustId] and [LocId] are the primary keys in the Customer and JobLocation

tables, but were also used as field names for the foreign key lookup fields in
the JobDetail table. (not the best database design, but I was very inexperienced
when first starting these databases) The field properties Required (No), and
Indexed (Yes, Duplicates Ok) apply to the foreign key fields in JobDetail.
**Okay, understood.

BEGINNING OF FORM_ frmJobDetail: CLASS MODULE
Private Sub Form_Activate()
DoCmd.RunCommand acCmdRefresh
End Sub
________________________________________________________________________________
_______________
Private Sub Form_Current()
If Me![Intl] = True Then
[SiteCityIntl].Visible = True
[SiteZipIntl].Visible = True
[SiteState].Visible = False
[SiteCity].Visible = False
[SiteZip].Visible = False
Else
[SiteCityIntl].Visible = False
[SiteZipIntl].Visible = False
[SiteState].Visible = True
[SiteCity].Visible = True
[SiteZip].Visible = True
End If
End Sub
________________________________________________________________________________
_______________
Private Sub LocId_AfterUpdate()
DoCmd.RunCommand acCmdSaveRecord
DoCmd.SetWarnings False
DoCmd.OpenQuery "qupdJobLocDetail", acNormal, acEdit
DoCmd.SetWarnings True
DoCmd.RunCommand acCmdRefresh
If Me![Intl] = True Then
[SiteCityIntl].Visible = True
[SiteZipIntl].Visible = True
[SiteState].Visible = False
[SiteCity].Visible = False
[SiteZip].Visible = False
Else
[SiteCityIntl].Visible = False
[SiteZipIntl].Visible = False
[SiteState].Visible = True
[SiteCity].Visible = True
[SiteZip].Visible = True
End If
Forms!frmJobDetail!LocId.SetFocus
End Sub
________________________________________________________________________________
________________
'This button still works -opens the customer form with linked record if

[CustId] has value, opens it in add mode if [CustId] is Null

Private Sub cmdOpenCustFrm_Click()
On Error GoTo Err_cmdOpenCustFrm_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmCustomer"
stLinkCriteria = "[CustId]=" & "'" & Me![CustId] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_cmdOpenCustFrm_Click:
Exit Sub
Err_cmdOpenCustFrm_Click:
MsgBox Err.Description
Resume Exit_cmdOpenCustFrm_Click
End Sub
________________________________________________________________________________
________________
'PROBLEM BUTTON -currently opens job location form in add mode if [LocId] is

null, but if [LocId] has a value still won't open job location form and show
related record. Until I added Bruce's error handler, resulted in following
error: '"The OpenForm action was canceled. You used a method of the DoCmd object
to carry out an action in Visual Basic, but then clicked Cancel in a dialog box.
For example, you used the Close method to close a changed form, then clicked
Cancel in the dialog box that asks if you want to save the changes you made to
the form."

Private Sub cmdOpenJobLoc_Click() 'THIS STARTS CODE COPIED FROM BRUCE
On Error GoTo cmdOpenJobLoc_Click_Err
'Check for Null value before opening form
If Not IsNull(Me![LocId]) Then 'BEFORE ADDING IsNull CODING

NULL [LocId] PRODUCED ERROR "Syntax error (missing operator)in query expression
'[LocId]='."

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmJobLocation"
stLinkCriteria = "[LocId]=""" & Me![LocId] & """"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Else 'Location ID is Null
'FOLLOWING CODE IS MINE REPLACING BRUCE'S (MsgBox "The Location ID field is

empty." GoTo Exit_cmdOpenJobLoc_Click)

DoCmd.OpenForm "frmJobLocation", , , , acAdd, acNormal
End If
'BACK TO BRUCE'S CODE
cmdOpenJobLoc_Click_Exit:
Exit Sub
cmdOpenJobLoc_Click_Err:
If Err.Number = 2501 Then 'Operation cancelled
Resume Next
Else
MsgBox "Error: " & Err.Number & " - " & Err.Description
Resume cmdOpenJobLoc_Click_Exit
End If 'THIS END IF WAS NOT IN THE CODE FROM THE POST

**Aha! So I did miss one. Good catch, bad me. :-(
'http://communities2.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=
microsoft.public.access.formscoding&mid=85c6ba1d-8ac2-4b62-80e3-268c665bac0c

End Sub 'END OF BRUCE'S CODE
________________________________________________________________________________
________________
END OF FORM_ frmJobDetail: CLASS MODULE
BEGINNING OF FORM_ frmCustomer: CLASS MODULE
________________________________________________________________________________
________________
Private Sub Form_Current()
If Me![Intl] = True Then
[CustZipIntl].Visible = True
[CustMainPhoneIntl].Visible = True
[CustMainFaxIntl].Visible = True
[CustCityIntl].Visible = True
[CustSt].Visible = False
[CustCity].Visible = False
[CustZip].Visible = False
[CustMainPhone].Visible = False
[CustMainFax].Visible = False
Else
[CustZipIntl].Visible = False
[CustMainPhoneIntl].Visible = False
[CustMainFaxIntl].Visible = False
[CustCityIntl].Visible = False
[CustCity].Visible = True
[CustSt].Visible = True
[CustZip].Visible = True
[CustMainPhone].Visible = True
[CustMainFax].Visible = True
End If
End Sub
________________________________________________________________________________
________________
Private Sub Form_Deactivate()
DoCmd.RunCommand acCmdSaveRecord

DoCmd.RunCommand acCmdRefresh

**I would replace these with:

If Me.Dirty Then Me.Dirty = False 'Saves record, if changed
Me.Refresh 'Refresh form

The reason being that issuing a Runcommand in the Deactivate event might not
perform the action on the form that you are leaving, but, instead, on the form
you are going to (I can't verify that this is what happens, but better safe than
sorry).

End Sub
________________________________________________________________________________
________________
END OF FORM_ frmCustomer: CLASS MODULE
BEGINNING OF FORM_ frmJobLocation: CLASS MODULE
________________________________________________________________________________
________________
Private Sub Form_Current()
If Me![Intl] = True Then
[SiteZipIntl].Visible = True
[SitePhoneIntl].Visible = True
[SiteFaxIntl].Visible = True
[SiteCityIntl].Visible = True
[SiteState].Visible = False
[SiteCity].Visible = False
[SiteZip].Visible = False
[SitePhone].Visible = False
[SiteFax].Visible = False
Else
[SiteZipIntl].Visible = False
[SitePhoneIntl].Visible = False
[SiteFaxIntl].Visible = False
[SiteCityIntl].Visible = False
[SiteCity].Visible = True
[SiteState].Visible = True
[SiteZip].Visible = True
[SitePhone].Visible = True
[SiteFax].Visible = True
End If
End Sub
________________________________________________________________________________
________________
Private Sub Form_Deactivate()
DoCmd.RunCommand acCmdRefresh

**And here:

If Me.Dirty Then
Me.Dirty = False
End If

End Sub
________________________________________________________________________________
________________
END OF FORM_ frmJobLocation: CLASS MODULE

Try making the suggested changes and see if your error message goes away. If
not, then there are other things that can be considered.

 

[Guest]

Bruce -
I finally got my forms to work properly using the code below. I isolated the problem through trial & error and found that it was the link criteria wording that was giving me problems

I would get errors using either of the following
stLinkCriteria = "[LocId]=""" & Me![LocId] & """
stLinkCriteria = "[LocId]=" & "'" & Me![LocId] & "'
But not using:
stLinkCriteria = "[LocId]=" & Me![LocId

I'll admit I have no idea what the difference between them is

Thanks for your help!

Private Sub cmdOpenJobLoc_Click(
On Error GoTo Err_cmdOpenJobLoc_Clic

If Not IsNull(Me![LocId]) And Not Me.NewRecord The
Dim stDocName As Strin
Dim stLinkCriteria As Strin

stDocName = "frmJobLocation
stLinkCriteria = "[LocId]=" & Me![LocId
DoCmd.OpenForm stDocName, , , stLinkCriteri
Els
DoCmd.OpenForm "frmJobLocation", acNormal, , , acFormAdd, acWindowNorma
End I

Exit_cmdOpenJobLoc_Click
Exit Su
Err_cmdOpenJobLoc_Click
MsgBox Err.Descriptio
Resume Exit_cmdOpenJobLoc_Clic
End Su

 

[Bruce M. Thompson]

Response below.

Bruce -
I finally got my forms to work properly using the code below. I isolated the

problem through trial & error and found that it was the link criteria wording
that was giving me problems.

I would get errors using either of the following:
stLinkCriteria = "[LocId]=""" & Me![LocId] & """"
stLinkCriteria = "[LocId]=" & "'" & Me![LocId] & "'"
But not using:
stLinkCriteria = "[LocId]=" & Me![LocId]

I'll admit I have no idea what the difference between them is.

***The first two statements pass the value of "LocId" to the criteria expression
as a string value (hence the surrounding quotes/double quotes). The third passes
the value of "LocId" to the criteria expression as a number. I can't believe
that I didn't ask you about that ... wait a minute ... you DID state in your
message on 10/22 that the LocId field was a "primary key autonumber".
Apparently, that didn't connect or I would have caught it right away.

Thanks for your help!

***Or lack thereof. :-(

Private Sub cmdOpenJobLoc_Click()
On Error GoTo Err_cmdOpenJobLoc_Click

If Not IsNull(Me![LocId]) And Not Me.NewRecord Then
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmJobLocation"
stLinkCriteria = "[LocId]=" & Me![LocId]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Else
DoCmd.OpenForm "frmJobLocation", acNormal, , , acFormAdd, acWindowNormal
End If

Exit_cmdOpenJobLoc_Click:
Exit Sub
Err_cmdOpenJobLoc_Click:
MsgBox Err.Description
Resume Exit_cmdOpenJobLoc_Click
End Sub

I'm glad you got that worked out. I am a little bummed out about my not catching
that discrepancy when the opportunity was presented and I apologize for that.

Thanks for posting the follow-up, Michelle.

 

[Guest]

Bruce
You did help - my experiments with Is Null never worked so your code there was helpful
Thanks

----- Bruce M. Thompson wrote: ----

Response below

Bruce
I finally got my forms to work properly using the code below. I isolated th

problem through trial & error and found that it was the link criteria wordin
that was giving me problems

I would get errors using either of the following

stLinkCriteria = "[LocId]=""" & Me![LocId] & """
stLinkCriteria = "[LocId]=" & "'" & Me![LocId] & "'
But not using
stLinkCriteria = "[LocId]=" & Me![LocId

I'll admit I have no idea what the difference between them is

***The first two statements pass the value of "LocId" to the criteria expressio
as a string value (hence the surrounding quotes/double quotes). The third passe
the value of "LocId" to the criteria expression as a number. I can't believ
that I didn't ask you about that ... wait a minute ... you DID state in you
message on 10/22 that the LocId field was a "primary key autonumber"
Apparently, that didn't connect or I would have caught it right away

Thanks for your help

***Or lack thereof. :-

Private Sub cmdOpenJobLoc_Click(
On Error GoTo Err_cmdOpenJobLoc_Clic

If Not IsNull(Me![LocId]) And Not Me.NewRecord The

Dim stDocName As Strin
Dim stLinkCriteria As Strin

stDocName = "frmJobLocation

stLinkCriteria = "[LocId]=" & Me![LocId
DoCmd.OpenForm stDocName, , , stLinkCriteri
Els
DoCmd.OpenForm "frmJobLocation", acNormal, , , acFormAdd acWindowNorma
End I

Exit_cmdOpenJobLoc_Click

Exit Su
Err_cmdOpenJobLoc_Click
MsgBox Err.Descriptio
Resume Exit_cmdOpenJobLoc_Clic
End Su

I'm glad you got that worked out. I am a little bummed out about my not catchin
that discrepancy when the opportunity was presented and I apologize for that

Thanks for posting the follow-up, Michelle