Help with Macro?

[Randy]

I am running the following macro that opens a case number. Field is
called number. If the number exists I want it to open the form. I do
not want it to go to the form if the worker puts in a number that does
not exist. Right now it will open the form to a blank record.

How can I fix this?

Private Sub Command31_Click()
On Error GoTo Err_Command31_Click


DoCmd.GoToRecord , , acFirst

Exit_Command31_Click:
Exit Sub

Err_Command31_Click:
MsgBox Err.Description
Resume Exit_Command31_Click

End Sub

 

[Ken Snell [MVP]]

What you posted is not an ACCESS macro... macros in ACCESS are not VBA code
(other Office applications do use the term macro for VBA code, which causes
confusion...). Your question would be more appropriate for one of these
newsgroups:
microsoft.public.access.modulescoding
microsoft.public.access.modulesdaovba
microsoft.public.access.formscoding

Your description of "open the form" does not match what you've posted as the
code that you're running. The posted code will simply move the form to the
first record in the form's RecordSource. It does not look to see what "case
number" may have been entered by a user.

You'll need to post more details about what controls are on the form, what
the user is expected to do, and which "form" you're wanting to open.

 

[Randy]

Sorry Ken I am new to Access and just trying to learn.

This is the macro

OPENFORM

Form Name = Main Employments
View = Form
Filter Name =FINDNUMBER
Where Condition =
Data Mode =Edit
Window Mode =Normal

I am not sure what condition to use.

 

[Ken Snell [MVP]]

What is the SQL statement of the query "FINDNUMBER"? Is the user entering
the case number on a form?

I would suggest that you change your macro to these steps (I'm assuming that
the case number entered by the user is into a textbox named txtCaseNum on a
form named frmEnter):

Condition: DCount("*", "MainEmploymentsQuery", "[CaseNumberField]=" &
Forms!frmEnter!txtCaseNum) > 0
Action: OpenForm
Form Name: Main Employments
Filter Name: (blank)
Where Condition: [CaseNumberField] = Forms!frmEnter!txtCaseNum
Data Mode: Edit
Window Mode: Normal

Condition: . . .
Action: StopMacro

Condition: (blank)
Action: MsgBox
Message: There is no such case number.