Solved Is there a way to "copy" values in a subform to a new record.

[CuriousGeo]

Hello, I have a maintenance database for an instrument that stains biological samples on glass slides. There are a series of containers with different chemicals in them. Of these, a few have Lot numbers and Expiration dates that are necessary to keep track of. The reason for copying them from day to day is because the lots last several months at a time.

The items in the subform are added by using the "Add List" button, which adds the items by insert:

' see if BOTH Name and Date Field are entered
If Len(TechName) > 0 And Len(fldDatePerformed) > 0 Then
DoCmd.SetWarnings False
DoCmd.RunSQL ("Insert INTO tblStainData (FKMasterID, FKStainListID) SELECT " & Me.masterID & ", tblStainList.listID FROM tblStainList WHERE (((tblStainList.Default)=Yes))")
DoCmd.SetWarnings True
End If

Me.Refresh

End Sub

The items all come from the same table.

That table is below:


Two questions:
1. Am I doing this set-up correctly, or should the StainLot and Expiration be in a separate table?
2. Is there a way to copy those two values from those fields into subsequent (new) records, until the lot is used up and we change it?

 

[bastanu]

There are a few ways to copy them (VBA, domain aggregate functions, etc.). For example create a query sorted descending by DataID and in the properties make it a Top 1. That would give you the last record in the table. Now edit your insert to add the StainLot =dlookup("StainLot","qryTop1") and StainExpiration=dlookup("StainExpiration","qryTop1").

Cheers,
Vlad

 

[CuriousGeo]

There are a few ways to copy them (VBA, domain aggregate functions, etc.). For example create a query sorted descending by DataID and in the properties make it a Top 1. That would give you the last record in the table. Now edit your insert to add the StainLot =dlookup("StainLot","qryTop1") and StainExpiration=dlookup("StainExpiration","qryTop1").

Cheers,
Vlad

Thank you for your assistance! I took your suggestion to make a query looking for the Top value for each of the StainLot. It did find the top value. As I have two items I need to look for, I made 2 separate queries. Now I am running into a roadblock about how to edit the Insert query statement. I am barely literate with vba. Can you assit further? Thank you very much.

Here is the SQL for each item:
Note: FKStainListID = 5 (and 12) is used to differentiate between the two chemicals, 5=Hematoxylin, 12=Cytostain

SELECT TOP 1 tblStainData.FKMasterID, tblStainData.FKStainListID, tblStainData.StainLot, tblStainData.StainExpiration
FROM tblStainData
GROUP BY tblStainData.FKMasterID, tblStainData.FKStainListID, tblStainData.StainLot, tblStainData.StainExpiration
HAVING (((tblStainData.FKStainListID)=5) AND ((tblStainData.StainLot) Is Not Null) AND ((tblStainData.StainExpiration) Is Not Null))
ORDER BY tblStainData.FKMasterID DESC;

related Lookup statements: DLookUp("StainLot","qryTopHem") and DLookUp("StainExpiration","qryTopHem")

SELECT TOP 1 tblStainData.FKMasterID, tblStainData.FKStainListID, tblStainData.StainLot, tblStainData.StainExpiration
FROM tblStainData
GROUP BY tblStainData.FKMasterID, tblStainData.FKStainListID, tblStainData.StainLot, tblStainData.StainExpiration
HAVING (((tblStainData.FKStainListID)=12) AND ((tblStainData.StainLot) Is Not Null) AND ((tblStainData.StainExpiration) Is Not Null))
ORDER BY tblStainData.FKMasterID DESC;

related Lookup statements: DLookUp("StainLot","qryTopCyto") and DLookUp("StainExpiration","qryTopCyto")

 

[bastanu]

Why don't you create an append query in the query designer and look at its SQL?
Any way, see if this works:

DoCmd.SetWarnings False
'hem
DoCmd.RunSQL ("Insert INTO tblStainData (FKMasterID, FKStainListID,StainLot,StainExpiration) SELECT " & Me.masterID & ", tblStainList.listID ,DLookUp("StainLot","qryTopHem") As StainLotHem ,DLookUp("StainExpiration","qryTopHem") As StainExpHem FROM tblStainList WHERE (((tblStainList.Default)=Yes))")
'cyto
DoCmd.RunSQL ("Insert INTO tblStainData (FKMasterID, FKStainListID,StainLot,StainExpiration) SELECT " & Me.masterID & ", tblStainList.listID ,DLookUp("StainLot","qryTopCyto") As StainLotCyto ,DLookUp("StainExpiration","qryTopCyto") As StainExpCyto FROM tblStainList WHERE (((tblStainList.Default)=Yes))")
DoCmd.SetWarnings True

Cheers,

 

[Pat Hartman]

There is no need to copy data from row to row. You need to make a table to hold your chemical inventory. That is where the batch or lot information goes as well as the expiration date. You also need a field to hold an "empty" indicator to flag the row as do not use once you have exhausted that bottle.

On the subform, you choose from a combo the type of chemical and the code logs the ID of the record for that chemical with the oldest expiration date > today that is not flagged as empty. So if you have three bottles of Hematoxylin with dates of 4/30/2021, 8/31/2021, 12/31/2021 and today is 2/28/2021, the query will choose the bottle dated 4/30 unless it is empty in which case it will choose the one dated 8/31.

Then your form can use a query with a left join to the inventory table that selects the batch/lot and expiration date. Both of these controls should be marked as locked since you do NOT want to accidentally change them on this form. As soon as you choose the chemical from the combo, your code will populate the ID it chose and the details for that item will automatically show on the form.

 

[CuriousGeo]

Pat Hartman, thank you for that insight. I did think I should put the chemicals in a separate table. Let me give this a try. I had tried it before, but I just couldn't figure out how to populate the actual Lot and Expiration date fields into my subform, using the chemical ID from another table

 

[Pat Hartman]

You pick the ID with a combo based on chemical. If you don't have a lot of different batches/lots in stock at one time, you can sort the list so that the one of each chemical you want to pick sorts to the top. Otherwise, the combo just picks the chemical and your code picks the batch/lot and populates the ID field.

It is the query with the join that does the magic of filling in the related fields. Again, don't forget to lock the fields from the chemical look up table so they cannot be changed accidentlly.
Here's an example that shows how this works

 

[CuriousGeo]

Thanks once again Pat! I did make the modifications to my tables and subform. It looks to be working as I wanted. And also thanks for your instructional database you attached in the previous message.

 

[gemma-the-husky]

Hello, I have a maintenance database for an instrument that stains biological samples on glass slides. There are a series of containers with different chemicals in them. Of these, a few have Lot numbers and Expiration dates that are necessary to keep track of. The reason for copying them from day to day is because the lots last several months at a time.

It's tricky this. It's a similar issue to managing product serial number registration. In the end it comes down to considering that your inventory is not homogeneous, and that you need to include some batch control. I don't know about copying the details daily. I don't think you should do that. But if you have say, different jars of citric acid, it's not enough to know that you used citric acid, you ned to be able to pinpoint the particular jar of citric acid you used in a given process. So your inventory allocation is more complicated than for most systems. I would have thought.

Anyway, you seem to have made a lot of progress with @Pat Hartman assistance.

 

[CuriousGeo]

It's tricky this. It's a similar issue to managing product serial number registration. In the end it comes down to considering that your inventory is not homogeneous, and that you need to include some batch control. I don't know about copying the details daily. I don't think you should do that. But if you have say, different jars of citric acid, it's not enough to know that you used citric acid, you ned to be able to pinpoint the particular jar of citric acid you used in a given process. So your inventory allocation is more complicated than for most systems. I would have thought.

Anyway, you seem to have made a lot of progress with @Pat Hartman assistance.

It's really not overly complicated, but that's probably because I work with this procedure/setup everyday. As far as different bottles of material open and in use, we only ever have one bottle of one chemical open and in use at a time. And when we purchase them, we get 2 or 3 containers, and they are all the same lot number. But, yes I could see the rare instance where we might have 2 lots open at the same time.

 

[Pat Hartman]

You're welcome. Glad to be of help. I've designed several apps for manufacturers that used heat/batch/lot and expiration dates for materials and so had to be careful to track exactly which was used where. One of the apps was for Pratt & Whitney. It managed testing logs and kept track of all the materials that went into every engine so that if there was an engine failure, all other engines with components from the same heat/batch/lot could be quickly identified and checked along with the testing history. That was a pretty fascinating application. I learned a lot about destructive and non-destructive testing and the things that companies do to keep us safe.