Subform displays previous record from main?

[wobrown]

Greetings, I've been teaching myself Access and VB to establish a chain of custody tracking database. I'll hopefully be able to attach the current version of my .mdb so that I might receive some guidance regarding an issue that suddenly appeared. Here's an outline of my intentions and the issue that's come up:

From the switchboard,
1) "Record Pipette Collection From Lab for Calibration" Opens frmCollection, where user scans pipette barcode into the SN field. Data about that pipette then populates the subform. This way, fields like 'lab location' can be verified (e.g., was this collected from the lab it was assigned to).

2) "Record Calibrated Pipette Return from Metrology" Opens frmReceivedPipettes, where user scans in SN and the collection event recorded in frmCollection is retrieved along with data about that pipette (subfrmLIMSpipettes_...)

Both 1) and 2) were working quite well, and so I began to build in more features.
But here is my current problem:
Upon opening frmCollection, when the first SN is scanned in, the associated data appears in the subform just as intended. However, after advancing to a new record and entering a new SN, the subform shows data for the pipette from the previously entered 'collection event'! This is always the case as additional pipette collections are recorded.
Interestingly, when navigating back to previously entered records, the subform data matches up again (i.e., no more mismatch to record entered previously).

In short, I can't figure out why the subform populates the data from the previous record only as new records are entered.

I've messed around with the code a lot and don't know why it isn't working and cannot fix it.

If anyone is willing to have a look at the db I've added, any assistance will be greatly appreciated. I do know that my VB is messy, partly due to my efforts to get things working, which they had been for a bit until this suddenly happened and I haven't yet been able to resolve it.

More thanks, in advance!

 

[boblarson]

Well, first thing up is that you should be storing the SN in the tblCollections table but not in the other. The ID field from tblCollections shouild be in the tblLIMSPipettes table. That would be the foreign key from the tblCollections.

So, how are these related to each other? Is this really a ONE-TO-ONE situation (like you actually have it set now) or can there be more than one entry in tblLIMSPipettes for a particular entry in tblCollections? If it is a One-to-many or many-to-many then it should be set up like I said, with the ID and not the SN.

 

[wobrown]

Thanks, Bob!

I had felt that I probably shouldn't be storing the SN in both tblLIMSpipettes and tblCollection, but...hmmm...

There are a finite number of pipettes, each with a representative record in tblLIMSPipettes. For each pipette, many calibrations will occur over time. A tblCollection record is created upon pickup from lab, and an associated tblReceivedPipettes record is created when the pipette is returned from calibration and returned to the lab.

So, to answer your question, "can there be more than one entry in tblLIMSPipettes for a particular entry in tblCollection," no, but the converse is true (as each pipette can be collected and received numerous times over time).

One tblCollection record/event cannot be associated with more than one tblLIMSpipettes. I'm trying to track each individual pipette as I ensure that they get calibrated.

I will think more about this in the evening and over the weekend. I welcome any further comments and greatly appreciate the assistance!

It sounds like the relationships issues you bring up may be the root of the problem I initially posted about?

Kinds Regards,

- wobrown

 

[boblarson]

Just remember that the normal way it works if you have a one-to-many situation, the one side (parent) is used on the main form and the many (children) are on the subform. And the parent's Primary Key is stored in the Child table as the Foreign Key.

 

[wobrown]

So I have my frmCollection set up backwards. I'll rebuild another frmCollection when I have a chance and will let you know how it goes. It's strange that the way it is set up now worked for a while, but this should be fixed in any case, it sounds.

It follows that to normalize the frmReceivedPipettes (which currently works well), tblLIMSpipettes should be the main form data source, with tblCollection and tblReceivedPipettes providing to the subforms.

Cool.

More thanks, and have a great, safe weekend!

Thank you so much, Bob!

 

[wobrown]

I ended up changing a .requery to .refresh and the problem was solved. Thanks, again