Solved Command Button not working properly

[asteropi]

I have 2 identical buttons and one is working properly and the other is not.

I have a form (SuppliersListF) that has a button to open another fom (SuppliersInfoF).
When it opens I need it to be unfiltered, but also find the specific record I'm asking. However this one will always go to #1 no matter which record it's showing

So I have this code in the VBA (translated)

Private Sub SupplierInfoBtn_Click()
DoCmd.OpenForm "SuppliersF"
DoCmd.FindRecord SupplierID


End Sub

I have no idea why it's not working. Its twin button on another similar form (CustomersListF -> Customer Info) works just fine, unfilered and finds the record, so I don't know why this one doesn't. I tried copying it but also creating from scratch but it will always start from #1, and not the record I'm asking

Any ideas?

 

[Gasman]

I would think because you are running code in one form that is meant to run in the other?
I have no idea as to why your customer form would work with that logic?
Normally a find would be SupplierID = something?
I would likely open the form with that criteria.

 

[asteropi]

tried this one too. Still goes to #1

 

[Gasman]

OK, think of what this code is doing.
It is running in form a
It opens form b with the first line.
It is STILL running in form a, so looks for that record in form a.
You need a way of find that record in form b.

Either use the Where argument of the open form, or pass in the criteria and carry out the find in form b if a value has been passed in via openargs.

 

[asteropi]

Either use the Where argument of the open form, or pass in the criteria and carry out the find in form b if a value has been passed in via openargs.

If I used the Where argument it finds the record but it's filtered.
I did not understand the second option you gave me

 

[The_Doc_Man]

You might have to consider that when you are using DoCmd.xxx, it implicitly is working in the context of the form running the code, NOT the context of the form you opened. You can't use Me. to refer to form X unless your code is running on form X.

If you open form X from your current form, you might be able to build an object reference to form X. If you do that and you know some value that you wanted to find in the data for form X, then you could try this from your command button.

DIM frmX as Access.Form
...
SET frmX = Forms("X")
frmX.recordsetclone FindFirst( "SupplierID=" & some-value )
frmX.recordset.bookmark = frmX.recordsetclone.bookmark
...

Note that if you built a combo box to find a particular record and used the combo box wizard to do that, you would see the code dealing with the form's .RecordSet and .RecordSetClone properties that both point to the recordset bound to that form. This DOES assume that the FindFirst actually found something, so I didn't include error checking here.

 

[Gasman]

If I used the Where argument it finds the record but it's filtered.
I did not understand the second option you gave me

It is only filtered. When you remove the filter, all the data is there.
Google OpenArgs for the other method.

 

[Gasman]

Perhaps walk your code to see what it is actually doing?

 

[asteropi]

You might have to consider that when you are using DoCmd.xxx, it implicitly is working in the context of the form running the code, NOT the context of the form you opened. You can't use Me. to refer to form X unless your code is running on form X.

If you open form X from your current form, you might be able to build an object reference to form X. If you do that and you know some value that you wanted to find in the data for form X, then you could try this from your command button.

DIM frmX as Access.Form
...
SET frmX = Forms("X")
frmX.recordsetclone FindFirst( "SupplierID=" & some-value )
frmX.recordset.bookmark = frmX.recordsetclone.bookmark
...

Note that if you built a combo box to find a particular record and used the combo box wizard to do that, you would see the code dealing with the form's .RecordSet and .RecordSetClone properties that both point to the recordset bound to that form. This DOES assume that the FindFirst actually found something, so I didn't include error checking here.

I guess it gives an error with FindFirst. And this is quite advanced for me, so I don't know how to fix it

 

[Gasman]

I think doc might have missed a . connecting recordsetclone and findfirst?

 

[cheekybuddha]

Try it more like:

Private Sub SupplierInfoBtn_Click()

  DoCmd.OpenForm "SuppliersF"
 
  With Forms.SuppliersF
    .RecordsetClone.FindFirst "SupplierID = " & Me.SupplierID
    If Not .RecordsetClone.NoMatch Then
      .Bookmark = .RecordsetClone.Bookmark
    Else
      MsgBox "SupplierID not found"
    End If
  End With

End Sub

(NB untested!)

 

[asteropi]

Try it more like:

Private Sub SupplierInfoBtn_Click()

  DoCmd.OpenForm "SuppliersF"

  With Forms.SuppliersF
    .RecordsetClone.FindFirst "SupplierID = " & Me.SupplierID
    If Not .RecordsetClone.NoMatch Then
      .Bookmark = .RecordsetClone.Bookmark
    Else
      MsgBox "SupplierID not found"
    End If
  End With

End Sub

(NB untested!)

worked like a charm!!! thank you so much!

 

[cheekybuddha]

worked like a charm!!! thank you so much!

Did you understand what is happening in the code?

Private Sub SupplierInfoBtn_Click()

  DoCmd.OpenForm "SuppliersF"                                   ' Open the form
 
  With Forms.SuppliersF                                         ' Reference the opened form
    .RecordsetClone.FindFirst "SupplierID = " & Me.SupplierID   ' Create an expression using SupplierID from THIS form and search the RecordsetClone of opened form
    If Not .RecordsetClone.NoMatch Then                         ' Test whether anything was found
      .Bookmark = .RecordsetClone.Bookmark                      ' If found, set the actual recordset to the correct record
    Else
      MsgBox "SupplierID not found"                             ' otherwise inform user that no match was found
    End If
  End With

End Sub

 

[Josef P.]

Note:
If only one record is required:

DoCmd.OpenForm FormName:="SuppliersF", WhereCondition:="SupplierID = " & Me.SupplierID

 

[asteropi]

Did you understand what is happening in the code?

Private Sub SupplierInfoBtn_Click()

  DoCmd.OpenForm "SuppliersF"                                   ' Open the form

  With Forms.SuppliersF                                         ' Reference the opened form
    .RecordsetClone.FindFirst "SupplierID = " & Me.SupplierID   ' Create an expression using SupplierID from THIS form and search the RecordsetClone of opened form
    If Not .RecordsetClone.NoMatch Then                         ' Test whether anything was found
      .Bookmark = .RecordsetClone.Bookmark                      ' If found, set the actual recordset to the correct record
    Else
      MsgBox "SupplierID not found"                             ' otherwise inform user that no match was found
    End If
  End With

End Sub

Almost. I wasn't sure what the Recordsetclone did, so thank you for explaining

 

[asteropi]

I needed it unfiltered to travel easily to each order I have placed with each supplier. The Where command, makes it filtered and I need to close and open the next one each time

Why not just use the where argument of the OpenForm method??????

 

[asteropi]

I don't know if this is connected to the first problem, but while you guys solved it so I can open it to a speficic record unfiltered, now it seems I can't register a new record through the forms

 

[sonic8]

Just a side note unrelated to the primary problem:
I strongly recommend not to use anything other than ASCII characters in VBA. The VBA *Environment* does not support Unicode. So, inside the VBA Environment you can only use ASCII and the characters of the current ANSI default code page. The latter means, your application may have severe issues if it is opened on a computer with a different ANSI default code page.

 

[asteropi]

I don't know if this is connected to the first problem, but while you guys solved it so I can open it to a speficic record unfiltered, now it seems I can't register a new record through the forms

Nevermind, fixed it myself

 

[cheekybuddha]

Nevermind, fixed it myself

Please consider sharing here how you fixed it.

It will be useful for others who might have the same problem in the future.