look up record using ste values from one form on another form

[Greg C]

it did not work here is the code i am using with the code
you sent
the command below operates off of a command button to find
the record i want i am searching using a Numeric value
beginning at 1000, currently i have 3 records in there as
this is a new database and i am running tests and
debugging before giving it out to the users.



Private Sub FindRecord_Click()
On Error GoTo Err_FindRecord_Click

StandardQuoteJobNum.RecordsetClone.FindFirst "Forms!
[StandardQuoteJobNum].Quote Number = " & QuoteNumber & "
AND Forms![StandardQuoteJobNum].Quote Number Rev= " &
QuoteRev & ""
StandardQuoteJobNum.Bookmark =
StandardQuoteJobNum.RecordsetClone.Bookmark
acMenuVer70

Exit_FindRecord_Click:
Exit Sub

Err_FindRecord_Click:
MsgBox Err.Description
Resume Exit_FindRecord_Click

End Sub

-----Original Message-----
the following code snippet finds a record on the
StandardQuoteJobNum based on your entries on the
QuoteInputBox form:

StandardQuoteJobNum.RecordsetClone.FindFirst "[SearchCrite r
ia1]='" & txtTextBox1 & "' AND [SearchCriteria1]='" &
txtTextBox2 & "'"
StandardQuoteJobNum.Bookmark =
StandardQuoteJobNum.RecordsetClone.Bookmark

if you are searching based on numeric values, remove the
single quotes and cast the value of the two textboxes to
the appropriate type (e.g. CINT(txtTextBox1))

hope this helps

-----Original Message-----
Morning All,

Here's what i need to do. I have a Form
StandardQuoteJobNum based on the tbl Standard Quote Sheet,
it is opened by clicking a command Button on the Main
form. i have also created a QuoteInputBox form that has 2
unbound text boxes on it that opens when the
StandardQuoteJobNum form opens what i want the
QuoteInputBox form to do is when i put in a value in the
text controls to find the corresponding record on the
StandardQuoteJobNum Form and set the focus on that record
and then close the QuoteInputBox form when it is done, if
the record is not found or does not exist i have an error
msgbox that i have created to popup and let the user know
that there is no such record. i know how to use the find
record method but i am not sure how to make the data from
the text boxes look up the record on the
StandardQuoteJobNum Form.

any help would be great Have A Great 4th of July.

Greg

 

[Emilia Maxim]

it did not work here is the code i am using with the code
you sent
the command below operates off of a command button to find
the record i want i am searching using a Numeric value
beginning at 1000, currently i have 3 records in there as
this is a new database and i am running tests and
debugging before giving it out to the users.



Private Sub FindRecord_Click()
On Error GoTo Err_FindRecord_Click

StandardQuoteJobNum.RecordsetClone.FindFirst "Forms!
[StandardQuoteJobNum].Quote Number = " & QuoteNumber & "
AND Forms![StandardQuoteJobNum].Quote Number Rev= " &
QuoteRev & ""
StandardQuoteJobNum.Bookmark =
StandardQuoteJobNum.RecordsetClone.Bookmark
acMenuVer70

Exit_FindRecord_Click:
Exit Sub

Err_FindRecord_Click:
MsgBox Err.Description
Resume Exit_FindRecord_Click

Greg,

when putting together a WHERE clause - i.e. search criteria - for a
recordset, you need the table field names, not the form controls. IOW,
you search actually in the table, not in the form. Plus the code you
have is very difficult to debug, I'll allow myself some improvements.
Try this:

Private Sub FindRecord_Click()
On Error GoTo Err_FindRecord_Click

Dim strWhere As String
Dim rst As DAO.Recordset

'It is not clear if the table field is called Quote Number
'or QuoteNumber - if the former, you must enclose the
'name in square brackets.
'I assume here, the table field is QuoteNumber
'I assume further, the values to be searched are
'in controls on the current form.
strWhere = "QuoteNumber = " & Me!QuoteNumber _
& " AND [Quote Number Rev] =" & Me!QuoteRev

'This is for testing if the criteria is OK
MsgBox "Criteria is: " & strWhere, 0

Set rst = Forms("StandardQuoteJobNum").RecordsetClone

rst.FindFirst strWhere
'You have to specify the collection. Even if Access
'would find the form, it takes more time if you only
'use the form name
Forms!StandardQuoteJobNum.Bookmark = rst.Bookmark

rst.Close
Set rst = Nothing

The code will work only if there will always be a record with the
given criteria. If this is not the case, post back.

Best regards
Emilia

Emilia Maxim
PC-SoftwareService, Stuttgart
http://www.maxim-software-service.de