FindFirst Query

[damian]

Hi

I’m attempting to open a form at a certain record where the value of an unbound textbox (txtBarcode) is equal to the primary field (signinID).

All works well until I attempt to modify the OnLoad, OnOpen events etc of the form that I’m finding the record within (ContractorSignoutDetailsForm).

Due to the line:

DoCmd.OpenForm "ContractorSignoutDetailsForm", , , , , acHidden

the OnLoad, OnOpen events etc kick in prematurely resulting in the whole database hanging – is there any practical way of getting around this so that the form dosn't have to be loaded at this stage?

Private Sub txtBarcode_AfterUpdate()

Dim strCriteria As String
Dim Cancel As Boolean
Dim rs As Object

    strCriteria = "[signinID] =" & Me.txtBarcode
DoCmd.OpenForm "ContractorSignoutDetailsForm", , , , , acHidden
    
    Set rs = Forms!ContractorSignoutDetailsForm.Recordset.Clone

    rs.FindFirst (strCriteria)
  
If rs.NoMatch Then
MsgBox "No entry found"
      DoCmd.Close acForm, "ContractorSignOutDetailsForm"
            Me.txtBarcode.SetFocus
            
    Else
        'MsgBox "Entry found"
        DoCmd.OpenForm "ContractorSignoutDetailsForm"
        Forms!ContractorSignoutDetailsForm.Bookmark = rs.Bookmark
        Me.txtBarcode.Undo
        DoCmd.Close acForm, Me.Name
    
    End If

Set rs = Nothing

End Sub

 

[Galaxiom]

Shouldn't Recordset.Clone be RecordsetClone ?

 

[DCrake]

From what I can see you are going about this a long winded way. you are attempting to open a form to test for a record whilst the form is hidden. Then checking the find first to see if the record exists then if it does show the form.

Surely the easiest way is to inerrogate the forms underlying query/table first. So if the form is bound to a query call qryDetails you would use a DLookup() to see if the record exists, then if it does open the form using the provided filter arguments. If not bring up a dialog box to say that record does not exists.

David

 

[damian]

Shouldn't Recordset.Clone be RecordsetClone

? Seems to work either way Galaxiom.

David - form is unbound, does this mean DLookup should still be used?

 

[LPurvis]

>> "Shouldn't Recordset.Clone be RecordsetClone"

Two different objects of the same type.
The code will work either way because of the object variable used to assign the object to it.
With RecordsetClone you wouldn't need the dedicated variable.
However the later use of a bookmark based on this when ContractorSignoutDetailsForm seems to have been opened again is questionable.

Is there a reason that you're simply navigating to the record you want as opposed to filtering to it?
Having a form loaded with all available records is excessive. Filtering down to the one you want offers the user that record without the overhead of the other rows.

So either way (opening the form before of after) a filtered form would be preferable.

As to whether to check first or not... Well it doesn't make a big difference. But if you check first, find the record and then launch the form - you've hit the database twice for that same data. If you open the form, if the filtered record isn't found then close without displaying - otherwise show the form, then you get essentially the same result with one hit inevitably.
Not a big thing really - if you application were to expand to many users then that kind of database consideration becomes important.

As for your issue - yes your loading code will always run when the form opens.
This wouldn't be so much of a problem though if the form is filtered - as you don't need to perform external actions on the form. You could code the form to close itself if it opens with an empty recordset.
And of course making use of the OpenArgs parameter would allow you to send information to the form for it to potentially decide to behave differently (or indeed filter itself instead of in your launching code).

Cheers.

 

[damian]

Went with DLookup for now, effectve and easier for me. Thanks for your advice. Code for reference:

Private Sub txtBarcode_AfterUpdate()

Dim strCriteria As String
Dim Cancel As Boolean
Dim rs As Object
Dim valueSignInID As Integer

strCriteria = "[signinID] =" & Me.txtBarcode

valueSignInID = Nz(DLookup("signinid", "ContractorSignOUTDetailsQuery", "signinid = txtBarcode"))

If Nz(valueSignInID, 0) = 0 Then
Me.txtBarcode.SetFocus

Else
DoCmd.OpenForm "ContractorSignoutDetailsForm", , , strCriteria

Me.txtBarcode.Undo
DoCmd.Close acForm, Me.Name
End If

Set rs = Nothing

End Sub