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

 

[Pat Hartman]

tried this one too. Still goes to #1

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

 

[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

 

[Pat Hartman]

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

That means you must have filtering on the results form also. As long as you have only a small set of data, downloading all rows of a table or query isn't a problem. It isn't until you get to large recordsets where you see the flaw in this method.

The better solution then, is to pass the ID into the opening form using the OpenArgs. Then in the Load event, you can use the find to position the record to the ID passed in. After that, how are you finding the next record? Do you have a combo or a search box?

Do not confuse a popup form (which you seem to have) with a subform. A subform is connected to the main form on which it sits by the master/child links of the subform control. That allows Access to automatically populate the FK when you add a record using the subform. For a popup form, you get no help, you need to populate the FK yourself. To do this, add one line of code to the on Insert event of the popup form:

Me.MyFK = Me.OpenArgs

If this solves your problem with populating the FK, then your concept is flawed and you need to rethink how the popup form is used because you said you don't want to go back to the base form to add a new record but you will have to because the OpenArgs is always only going to hold the ID of the record that is visible on the main form.

When I do this, I put the name of the master record in an unbound control on the header of a continuous form. The popup would never be a main form (bound to the base form's record) with a subform. If the popup really needs to be a main form with a subform, then, the above solution will link the main form record back to the base form record. BUT the subform on the popup MUST still have the master/child links set in order to link to the record on the popup main form. To put this into context.

Assuming the base form is the Customer. The popup is an Order. The subform is the Order details.

So when you add a new order on the popup main form, you need to capture the CustomerID from the OpenArgs. Use OpenArgs rather than hard coding a reference to the base form. That gives you the flexibility of opening the popup from more than one base form if that becomes necessary. Then the subform on the popup uses the standard method of the master/child links.

You need to make the FK in both the popup main form as well as the popup's subform required at the table level AND remove 0 as the default value. The mainform record of the popup form should NEVER be able to be saved without the FK to it's parent record.

 

[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.