Form Lookup

[BFreshour]

On entry to my form it opens a new record for the user to add. At the top of this form I have two fields that are entered first. A Store # and a Date. I have an index setup with those 2 fields that does not allow duplicates. This is what I would like to do.

They enter their store number and tab to the date field. Then enter the date and tab out of that field. Now I want it to search the database for a record matching the Store # and Date they entered. (Otherwise they are going to fill out this long form then try to save it and it's going to tell them there is already a record). If successful, I would like it to load that record. I'm fairly sure I need to code it under On Exit of the Date InputBox but the only DoCmd function I've found (FindRecord) only allows you to specify one criteria, not two.

Does anyone know if this is possible, if so, could you give me some tips?

 

[Jack Cowley]

I think this will work for you in the After Update event of your Data field:

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[StoreNo] = " & Me![StoreNoField] & " And [ADate] = #" & Me![DateField] & "#"
Me.Bookmark = rs.Bookmark

[This message has been edited by Jack Cowley (edited 03-01-2002).]

 

[BFreshour]

Access 97 is screwing me up I think... but this is what it has to be done it.

I tried your code and it wouldn't accept rs = Recordset.Clone but it would accept rs = RecordsetClone so I used that.

Then it wouldn't accept the FindFirst statement, but it will accept just a plain Find. I tried using it and now I get "Object doesn't support this property or method". This is what my code looks like... The top piece is commented out and is what I was working on trying to get this to work...

Private Sub DATE_Exit(Cancel As Integer)
    'Dim rst As Object
    'Dim strSLIC As String
    'Dim strDATE As String
    
    'If (IsNull(Me.SLIC)) Or (Me.SLIC = "") Or (Me.SLIC = 0) Then Exit Sub
    'If (IsNull(Me.DATE)) Or (Me.DATE = "") Then Exit Sub
    
    'strSLIC = Me.SLIC
    'strDATE = Me.DATE
    
    'Set rst = Me.RecordsetClone
    
    Dim rs As Object

    Set rs = Me.RecordsetClone
    rs.Find "[SLIC] = " & Me![SLIC] & " And [DATE] = #" & Me![DATE] & "#"
    Me.Bookmark = rs.Bookmark
End Sub

 

[David R]

This method uses combo boxes, but you can translate it to unbound text boxes:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q100132

Also, to use a bound control, try this:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q136123

HTH,
David R

 

[BFreshour]

I am very close to getting this. It's finding the Record but for some reason giving me the following error:

"Microsoft Access Run-time Error '2105':
You can't go to the specified record.

You may be at the end of a recordset."

Here is my code:

Dim rs As Object
    Dim varBookmark As Variant
    
    Set rs = Me.RecordsetClone
    rs.Findfirst "SLIC = " & Me![SLIC] & "AND DATE = #" & Me![DATE] & "#"
    If rs.NoMatch Then
        Exit Sub
    Else
        varBookmark = rs.Bookmark
        Me.Bookmark = varBookmark
    End If

Any suggestions? I've done some tests and I know it's finding the record so I don't understand how it could be at the end of the recordset...

 

[BFreshour]

Just in case it matters, here are my form properties for the 'Data' tab.

 

[BFreshour]

Any help available on this yet? I'm desperantly seeking answers...

 

[BFreshour]

I figured out the problem.. I needed a Me.Undo before setting my Me.Bookmark to the record.