Sorry, I left something out
Change this line:
Set rstTestParts = CurrentDb().OpenRecordset("TestParts")
To
Set rstTestParts = CurrentDb().OpenRecordset("TestParts", dbOpenDynaset)
Also, just food for thought. You form would execute faster if you open the
recordset in the form's Load event and just do the navigation in the click
event. To do this correctly, you would declare your recordset at the module
level (At the top of the module right after the Option declarations).
Option Compare Database
Option Explicit
Option Base 0
Dim rstTestParts As Recordset
Then do this part in the form's Load event:
Set rstTestParts = CurrentDb().OpenRecordset("TestParts", dbOpenDynaset)
Now, as long as you don't manually change the value in Me.[Test Parts], all
you have to do in the click event is check to be sure you are not at EOF and
move to the next record. If you have occasion to modify the value in
Me.[Test Parts], you can handle that in the After Update event of Me.[Test
Parts]
Dim varCurrRec as Variant
varCurrRec = rstTestParts.Bookmark
rstTestParts.FindFirst "[PartNumber] = '" & Me.[Part Number] & "'"
If rstTestParts.NoMatch Then
rstTestParts.BookMark = varCurrRec
MsgBox "Part Number " & Me.[PartNumber] & " Not in Table"
End If
Now, all you need to do in the Click event of your button is
rstTestParts.MoveNext
If rst.EOF Then
MsgBox "No More Records in Table"
Else
Me.[Part Number] = rstTestParts![PartNumber]
End If
BK said:
I tried it and it's saying:
Operation not supported for this type of object, and the error is in this line
rstTestParts.FindFirst "[PartNumber] = " '" & Me.[Part Number] & "'"
:
Your life would be much easier if you would use bound forms.
Private Sub NextPartNumber_Click()
Dim rstTestParts As Recordset
Set rstTestParts = CurrentDb().OpenRecordset("TestParts")
If rstTestParts.RecordCount = 0 Then
Exit Sub
End If
If IsNull(Me.[Part Number]) Then
Me.[Part Number]) = rstTestParts![PartNumber]
Else
rstTestParts.FindFirst "[PartNumber] = '" & Me.[Part Number] & "'"
If Not rstTestParts.NoMatch Then
rstTestParts.MoveNext
If rst.EOF Then
MsgBox "No More Records in Table"
Else
Me.[Part Number] = rstTestParts![PartNumber]
End If
End If
End If
End Sub
:
It's an unbound form. Here is an update of what I have so far...it only
displays the first part number from the table each time I click Next Part
Number:
Private Sub NextPartNumber_Click()
Dim rstTestParts As Recordset
Set rstTestParts = CurrentDb().OpenRecordset("TestParts")
Dim frmTestControlPanel As [Form_Test Control Panel]
Set frmTestControlPanel = [Form_Test Control Panel]
frmTestControlPanel![Part Number] = rstTestParts![PartNumber]
rstTestParts.MoveNext
End Sub
:
Is this a bound form or an unbound form?
If it is a bound form, is it bound to the part number table or the BOM table?
:
I have form where I enter the part number (in a text box) and have several
buttons to download the Bill of Materials for that part number. It's a pain
having to enter each part number from a table each time, so I made a button
called Next Part Number. I want to move through the TestParts table and
display each part number in the text box on the form each time I click the
button. Here is what I have so far:
Thanks!
Private Sub NextPartNumber_Click()
Dim rstTestParts As Recordset
Set rstTestParts = CurrentDb().OpenRecordset("TestParts")
Dim frmTestControlPanel As [Form_Test Control Panel]
While Not rstTestParts.EOF
frmTestControlPanel![Part Number] = rstTestParts![PartNumber]
rstTestParts.MoveNext
Wend
End Sub
