Logical Error with recordsets 2

[bozic12]

Hey all,

I'm encountering a logical error that's kind of difficult to explain, but i'll do my best. I have 3 forms in which to input data for an assembly. I have another form to input an assembly number, which in turn fills in the forms with the data from the records in 3 repective tables that match the inputted assembly number. These are lines of code I use to have the controls on one of the forms fill in with a matching record:

Private Sub Command3_click()
Assembly = [Forms]![AssemblyNumber]![AssemblyNumber].Value

Set rst1 = CurrentDb.OpenRecordset("Bill of Materials&quot
count1 = DCount("[Assembly Number]", "Bill of Materials&quot
.
.
If count1 > 0 Then
rst1.MoveFirst
For i = 1 To count1
If rst1.Fields(1) = Assembly Then
record1 = i
Exit For
End If
rst1.MoveNext
Next i
End If
.
.
If record1 > 0 Then
Docmd.OpenForm "Bill of Materials", acNormal, , , acFormEdit
Docmd.GoToRecord acDataForm, "Bill of Materials", acGoTo, record1
Docmd.Close acForm, "AssemblyNumber", acSaveNo
Else
MsgBox "Sorry, there are currently no records with that Assembly Number.", _
vbInformation, "No Record"
AssemblyNumber.Value = ""
AssemblyNumber.SetFocus
Exit Sub
End If

My problem lies in the fact that as the recordsets movenext, they aren't in the same order as listed in the tables, which I find VERY odd. The result is that when I use the goto record, it goes to the wrong record. Here is an example
Bill of Materials tables has assembly numbers in this order:
123-456
123-567
8
333-3-333
FDD-4414
543
858
As the codes runs through the recordsets, it goes in this order:
123-456
123-567
8
333-3-333
FDD-4414
858
543

The last two records are flipped. Another very weird thing is that this happens in two of the tables, the order of the recordsets in the third is as they are in the tables.
Even when I change the order of the records in the table, the order doesn't change when moving through the recordsets via code. I'm obviously missing something because I am completely clueless and have exhaused all reasons I have thought of for this logical error in my code. I know my explanation here may be very confusing, but any help is much appreciated. Let me know if you need me to try and clear anything up.

Thanks,

Jeff

 

[vbajock]

You should look at the Recordsetclone property in the help file. You can sync your recordset with your form using this property. There's a great example code frag in there as well.

 

[DaltonVB]

From what I can gather. You want to open the "Bill of Materials" form if the Assembly number from the "Assembly Number" Form is in the "Bill Of Materials" table and have the form open up to that record.

Here is what I would do. You would have to substitute your table and field names but the example would work. After the form opens, you could add code to an event to remove the filter from the form to see other records.

Private Sub txtVendor_Click()
Dim stDocName As String
Dim stLinkCriteria As String

DoCmd.MoveSize 1, 1
'Name of the form you want to open
stDocName = "frmVendorDetail"
'the criteria you want to apply to the form
stLinkCriteria = "[Vendor]=" & "'" & Me![txtVendor] & "'"
'If the number exists in the table 'tblMasterList' then open apropriate forms
' that use the data from 'tblMasterList'.
' If the field (in this case [Vendor]) is numeric, the criteria would look
' like this "Vendor = " & txtVendor
If DCount("[Vendor]", "tblMasterList", "Vendor = '" & txtVendor & "'&quot > 0 Then
'First form to open with the criteria
DoCmd.OpenForm FormName:=stDocName, WhereCondition:=stLinkCriteria
End If

End Sub

 

[DaltonVB]

A much simpler way to do what you want would be to use the first persons response. This uses the standart DAO. If you are trying to use only ADO, there is another way to do this.

Here is some code from one of my forms.
Private Sub cmdButton_Click()
Dim rs As Object

DoCmd.OpenForm "frmTaskTrack"
' The form must be open befor you do this
Set rs = Form_frmTaskTrack.Recordset.Clone
'"ActiveTaskID" is numeric
rs.FindFirst "[ActiveTaskID] = " & lActiveTaskId
Form_frmTaskTrack.Bookmark = rs.Bookmark
End Sub

 

[bozic12]

vbajock and daltonvb,

I did end up using the recordsetclone method, I was unaware that it even existed! Thank you both much as I have solved my problem. I still don't know why my problem was occuring, but it doesn't matter now! Thanks again!

-Jeff