Open form based on combo box value w/error message for blank combo box

R

Ruth

I am working in an Access 2000 database that I've inherited. I'm
trying to clean up some of the non-working functions.

I am trying to open a form using the OnClick property for a button. On
a job order form, there is an unbound combo box [CmbContactID] that
gets its list from querying Table!ContactInformation.[CustomerCodes].
When populated, [CmbContactID] should provide the value to open
FrmContactInformation when [CmdOpenContactForm] (a button) is clicked.
I want an error message to display if the field is blank otherwise it
will open the form for the customer code that is displayed in the
box.


I have written a macro that
1) Checks [CmbContactID] for an entry and displays a message box
prompting the user to make a selection if the field is null
2) If null, it returns focus to the CmbContactID control
3) Stops the macro if CmbContactID is blank
4) Opens FrmContactInformation based on the value in CmbContactID

Everything works fine until step 4. If I put the condition in the
Condition field of the build grid (View, Conditions) I get an error
message, "OCCDB can't find the form 'FrmContactInformation' referred
to in a macro expression or Visual Basic code." When I put the
condition in the Where Condition field in the properties area for that
particular line of the macro, it seems to totally ignore the condition
and opens a new form (filtered so that you can't search for the
appropriate contact information).

If I use the wizard to create the button, it opens the Contact
Information Form just fine-if there is a value in the field.
Otherwise, I get the same behavior as step 4 above-- the form opens to
a new record and you can't search because it's a filtered view.

Here's what the code looks like for the wizard button:
Private Sub btnContact_Click()
On Error GoTo Err_ btnContact _Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "FrmContactInformation"

stLinkCriteria = "[CustomerCodes]=" & "'" & Me![CmbContactID] &
"'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_ btnContact _Click:
Exit Sub

Err_ btnContact _Click:
MsgBox Err.Description
Resume Exit_Command250_Click

End Sub

Converting the macro to a module produced this code (but I can't
figure out how to access it from the button-and it doesn't work 100%
anyway...):

Function mcrOpenContacts()
On Error GoTo mcrOpenContacts_Err

If (Eval("[Forms]![FrmJobEntry]![CmbContactID] Is Null")) Then
Beep
MsgBox "You must select a company first. Try again!",
vbInformation, "Select a company"
End If
If (Eval("[Forms]![FrmJobEntry]![CmbContactID] Is Null")) Then
DoCmd.GoToControl "CmbContactID"
End If
If (Eval("[Forms]![FrmJobEntry]![CmbContactID] Is Null")) Then
Exit Function
End If
DoCmd.OpenForm "FrmContactInformation", acNormal, "",
"[CustomerCodes]=[CmbContactID]", acEdit, acNormal


mcrOpenContacts_Exit:
Exit Function

mcrOpenContacts_Err:
MsgBox Error$
Resume mcrOpenContacts_Exit

End Function

I need to incorporate the working parts of both options into one
button click solution. Can anyone help?

Thanks!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top