Need help with "Next Record" button

[MarcieFess]

I've created a button to go to the next product in my product table.

The code:

Private Sub btnNextProduct_Click()
DoCmd.RunCommand acCmdRecordsGoToNext
End Sub

The problem is that it's not going to the next product in the Product table...it's going to the next record in the Store Products table...which is where we keep track of how many of a particular UPC in the Product table is in each particular store.

I want this button to go to the next UPC because when we get to where we have 25 stores in the database, we don't want to have to press "Next Product" 25 times just to get to the actual "next product".

Thanks for your help!

 

[MarcieFess]

Sorry I was unclear. I have a form, and this form contains all of the product information in tabs. The form also contains a subform, where we input the specific quantities of the product in each store location. The button in question is on the main form, not the subform, so I'm confused as to why it's going through the records in the subform not the main form.

 

[AccessMSSQL]

Hi Marcie:
Is your button on the main products information form? Make sure it's not on the subform.

You can try this:

Private Sub btnNextProduct_Click()
me.UPC.setfocus
DoCmd.RunCommand acCmdRecordsGoToNext
End Sub

 

[AccessMSSQL]

I just tried this in my copy and it works okay for me. Not sure why it's going to the next record in the store subform. Strange. There must be something else going on there.

 

[MarcieFess]

I knew there was a way to make sure it was focusing on the correct field. Thanks!

Yes, the button is on the main form not the subform.

 

[boblarson]

I just tried this in my copy and it works okay for me. Not sure why it's going to the next record in the store subform. Strange. There must be something else going on there.

DoCmds have limited scope. So, the best way to move a subform's recordset to another record is to refer to it directly:

Me.SubformControlNameHere.Form.Recordset.MoveNext

(where SubformControlNameHere is the name of the control on the parent form which houses/displays the subform, but not the subform name itself unless they share the exact same name.)

 

[AccessMSSQL]

Yes, thanks Bob. That's another way to do it, Marcy. In your case it would be this:

Private Sub btnNextProduct_Click()
me.Recordset.MoveNext
End Sub

since you are referring to the main form.

 

[boblarson]

Yes, thanks Bob. That's another way to do it, Marcy. In your case it would be this:

Private Sub btnNextProduct_Click()
me.Recordset.MoveNext
End Sub

since you are referring to the main form.

Yes, and what I posted is if you want the button on the main form instead of the subform. Sometimes is cleaner that way.

 

[MarcieFess]

Attached is a screen shot of the form showing the buttons on the main form, and a screen shot of the VBA code for 2 of the buttons. If I can get it solved for the NextProduct button I can do it for the Previous button.

The SAVE and NEW PRODUCT buttons are working fine.

You notice on the screen shot of the form, that this product is in 2 stores right now. Eventually it'll be 50. Clicking the Next button still pages through the sub-form.

I even created an entirely new form WITHOUT the subform; the behavior is still the same.

I don't understand this at all, since the UPC is the unique key for the Product table, which is what this form is based on...the sub-form is based on the Store Products table; they join by the UPC.

:banghead:

 

[MarcieFess]

I just solved part of it.

I had based the form on a query that joined the Product and StoreProduct tables.

I removed all references to tables and queries in the main form, EXCEPT to the Product form.

It is now behaving the way I would like it to behave.

A few tweaks and hopefully I can knock this form off my list...

 

[MarcieFess]

Yay it's all fixed. That was the problem. As soon as I removed all references to the StoreProducts table from the main form, it began to behave properly.

Thank you everyone!