I have one table that gets a number of records imported into it several times a month. There are two fields I am trying to establish query programming for that will likely have to reference some VBA. I am a bit of an Access noob who sometimes has a hard time grasping how to cobble things together.
For each record in this table, the “SamplePeriod” column will already be populated with a value for each record that is imported. For each record with an identical value in this SamplePeriod field, I want a sequential three digit count (001) to populate the “Case#” column of the same table. Whenever new cases with a matching SamplePeriod value are added, I want the count to continue where it left off for that specific SamplePeriod value.
What would the VBA look like for this and then how would I call on this VBA to apply to all records within my table where the Case# field is blank (as those would be the newly imported records that need their SamplePeriod field looked at to generate the next sequential # in the Case#). The sequential numbering doesn’t have to reference any other data in each record to be in a certain order; I just want to assign a number to all the new cases but I do want a sequential order for each unique SamplePeriod value.
Thanks for any and all guidance!
I feel like DMAX will be involved, but I get overwhelmed with trying to make sense of the VBA. I learn so much from seeing how proven VBA code works but I have had a hard time finding the VBA relationship I am looking for.
BobSacamano is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
1
If I understood your explanation correctly, you need to assign codes to new rows (records) after importing additional rows.
Take a look at the example.
There we take max current [Case#] for every SamplePeriod – key value for this operation – thru DLookUp
nz(DLookUp("max([Case#])","YourTable","SamplePeriod =""" & SamplePeriod &""""),0)
Than increment this value as integer and cust to format ‘001’.
UPDATE YourTable
SET YourTable.[Case#]
=right("000" _
& clng(nz(DLookUp("max([Case#])","YourTable","SamplePeriod =""" & SamplePeriod &""""),0))_
+1 _
,3)
where [Case#] is null or len([Case#])=0
;