Record Navigation (1 Viewer)

[Shipper225]

I am doing this in Access 2007.

I'm having an issue trying to move between records on a set form. The form is linked to a table where each order is created has multiple lines to it. When I try using the buttons I made to move between the records I view duplicate records until I finally get to a different WorkOrderID, I wasn't sure if anyone knew how to make the button jump between WorkOrderID fields instead of moving from record to record.

These are my two codes for next and previous:

Private Sub ButtonPrevious_Click()
On Error GoTo Err_ButtonPrevious_Click


    DoCmd.GoToRecord , , acPrevious

Exit_ButtonPrevious_Click:
    Exit Sub

Err_ButtonPrevious_Click:
    MsgBox Err.description
    Resume Exit_ButtonPrevious_Click
    
End Sub
Private Sub ButtonNext_Click()
On Error GoTo Err_ButtonNext_Click


    DoCmd.GoToRecord , , acNext

Exit_ButtonNext_Click:
    Exit Sub

Err_ButtonNext_Click:
    MsgBox Err.description
    Resume Exit_ButtonNext_Click
    
End Sub

 

[missinglinq]

The first question here is why do you have 'duplicate' Records?

Or are the Records only 'duplicate' in that they have the same WorkOrderID, but other data is different?

Linq ;0)>

 

[Shipper225]

The second one, there area few records with the same WorkOrderID but the actual details vary per line. For example I have only 26 ID's but have well over 4,000 records.

 

[missinglinq]

That's what I suspected. You have a single Table which is a major mistake that needs to be corrected before you do anything else.

You have to have two Tables, a WorkOrderTable and a DetailLineTable. The WorkOrderTable would have Fields to include the WorkOrderID (the Primary Key) and any other identifying data for that particular work order.

The DetailLineTable should have the WorkOrderID (as a Foreign Key), a DetailID and any data that is relevant only to that detail line.

The two Tables will then be linked by the WorkOrderID Field in a One-to-Many relationship, with the Work Orders being the One side and the Details being the Many.

You'd have a Main Form based on the WorkOrderTable and a Subform based on the DetailLineTable.

Once you have your Main Form created, add the second, Details Form, as the Subform, and Access will link the two, automatically.

Then place the code you started out with, in the Main Form, and you will be able to move from one Work Order to the next Work Order. And as you move from one Work Order to the next Work Order, the Details Records in the Subform will change appropriately.

Linq ;0)>

 

[Shipper225]

Took a bit but I did as suggested and separated the two tables. Works great thank you for the help.

 

[missinglinq]

Not bad for a 'shipping' type worker!

Good luck with your project!

Linq ;0)>