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]

"Greg C" <gregc@mgrstone.com> wrote:

>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
[url]http://www.maxim-software-service.de[/url]