Go to next (UNIQUE) record

[Westside1st]

Hello experts

I'm having an issue solving this puzzle

I have a query which include linked tables
(please check the uploaded Image)

In the form I had a command button that had a VBA code On Click

DoCmd.GoToRecord , , acNext

My issue is that the REQ_NO field contain a duplicate records because of the linked table ... So when I go to next record by clicking the command button it shows me the same record details multiple time

for example :

I'm in record no. 12 when I click next it moves to 13 when I click next again it moves to 14 ... but when I'm in record 29 and I click next it moves to 29 and when I click next again it moves to 29 and I click next for the forth time then it moves to 30 .
And that because 29 is a three records in the query.

So is there a way to tell the access to move from 29 to 30 and from 30 to 31 without going through all the duplicated record ???

Thank you all

 

[Micron]

In code, possibly use FindFirst method where REQ_NO > REQ_NO

Recordset.FindFirst method (DAO)

Office developer client VBA reference

learn.microsoft.com

 

[jackd]

Please show us your table design. What field/fields make records unique?

 

[Westside1st]

Even if there is a unique field .... The problem is that I'm going to see duplicated information when I click next because every records linked to multiple tickets and every tickets having the same details but linked to one record which is duplicated.... Sorry about the confusion .


I still didn't figure out the FindFirst method yet but I'm trying.

 

[Micron]

Post some data if you remain stuck and I'll take a crack at it - no pics of data please.

 

[Westside1st]

Sorry for that ....
I tried to upload my database but the file is large for the server so I upload it on mediafire .... I don't know if this is a violation .. putting an external link.

VOID

MediaFire is a simple to use free service that lets you put all your photos, documents, music, and video in a single place so you can access them anywhere and share them everywhere.

www.mediafire.com

Thanks

 

[Micron]

File uploads are not allowed here AFAIK, so it wouldn't matter how small it is. Some forums require that you zip the file so keep that in mind.
I have it now but there's no form and no code in what you posted.

 

[Westside1st]

I'm so sorry for the mistake .... Now the file is bigger

VOID_Tasks

MediaFire is a simple to use free service that lets you put all your photos, documents, music, and video in a single place so you can access them anywhere and share them everywhere.

www.mediafire.com

I couldn't make it smaller.

Regards

 

[Micron]

First I'll say that you don't really have duplicate records per se. #29 has 2 different ticket no's so the records are not dupes. One way to eliminate that is to exclude the query fields that cause it. Maybe that will work for you. It turns out that the button you refer to in post 1 is a record navigation button (I saw no other buttons which use GoTo), which means that IF there is a fix you need it for both directions - double trouble.

The reason your file is so large is that you are using attachment fields. This is not advised; you will likely eventually exceed the size limit for an Access db. Use file paths instead, or move your tables to another platform such as SqlServer.

Your tables are not designed correctly. If you've studied database normalization, you should review. If not, you should review. For example:
- the primary key of employess table is the wrong field
- many fields are in that table that don't belong there. Employees are people and don't have a file type.
- you appear to have fields in 2 or more tables that are the same (e.g._oldID), yet are not related. If data is edited on one side, chances are good that the other side will remain unchanged, thus be wrong.
You are always going to have issues if you don't fix the design, which probably means starting over. If you do, I suggest you learn and adopt a proper naming convention. You could redesign your database while continuing to use this db as is. Then the problem should go away with proper design.

I think the main problem here is that since the query has multiple records for (e.g. REQ_NO = 30) you should be using a form/subform design. Whatever the parent thing is, the multiple req's would be in the subform. However, I cannot tell from that query what the "one/single" parent thing would be and that's another problem.

If I have the time I'll try to see if there is a temporary solution but it probably won't be today.

 

[Westside1st]

Thank you Micron for your replay and all the information you've mentioned.

Your replay is more than enough.
I appreciate it.

I'll try to fix it by following your guidance.