By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,456 Members | 1,251 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,456 IT Pros & Developers. It's quick & easy.

Form to open another form to a particular record

P: n/a
I have two forms, frmSearch and frmMain. I want to be able to enter a job
number in a text box on frmSearch and when I enter it, open frmMain and go
to the record for the job number I've entered. This seems fairly simple but
I haven't been able to get it to work properly. Here's the code in
frmSearch. txtSearch is the input text box and JobNumber is the field I
want to select frmMain by.

Private Sub txtSearch_Exit(Cancel As Integer)
SearchJob = Me.txtSearch
DoCmd.OpenForm "frmMain", acNormal, , JobNumber = SearchJob
End Sub

When I enter the job number in frmSearch and hit Enter, frmMain opens but
instead of going to the job number record I want, it opens with a blank
record. The status bar reads "Record 1 of 1 (filtered)"

Any help will be greatly appreciated.
Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
The WhereCondition is a string.
You need to concatenate the value from the onto the string, like this:
"JobNumber = " & Me.txtSearch

Note that if JobNumber is actually a Text type field (not a Number type
field), you need extra quotes:
"JobNumber = """ & Me.txtSearch & """"

If is is a Number type field, set the Format property of txtSearch to
General Number so the user cannot enter a non-numeric value.

In general, it will be best to use the AfterUpdate event of the search box,
so the code only runs if the user types something, rather than just because
they visited the text box.

Result:

Private Sub txtSearch_AfterUpdate()
Dim strWhere As String

If Not IsNull(Me.txtSearch) Then
strWhere = "JobNumber = " & Me.txtSearch
End If

DoCmd.OpenForm "frmMain", , , strWhere
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Bob Sanderson" <sa*****@LUVSPAMsandmansoftware.com> wrote in message
news:Xn**********************************@207.69.1 89.191...
I have two forms, frmSearch and frmMain. I want to be able to enter a job
number in a text box on frmSearch and when I enter it, open frmMain and go
to the record for the job number I've entered. This seems fairly simple
but
I haven't been able to get it to work properly. Here's the code in
frmSearch. txtSearch is the input text box and JobNumber is the field I
want to select frmMain by.

Private Sub txtSearch_Exit(Cancel As Integer)
SearchJob = Me.txtSearch
DoCmd.OpenForm "frmMain", acNormal, , JobNumber = SearchJob
End Sub

When I enter the job number in frmSearch and hit Enter, frmMain opens but
instead of going to the job number record I want, it opens with a blank
record. The status bar reads "Record 1 of 1 (filtered)"

Any help will be greatly appreciated.

Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.