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,

 

[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

 

[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.