captbluefin
New member
- Local time
- Today, 07:45
- Joined
- Sep 13, 2023
- Messages
- 3
We run an environmental testing Lab. We get water samples directly from cities and towns, or we recieve samples from other labs that subcontract the work to us.
When a city of town send us water directly - they are what we consider the primary client.
When another lab sends us the sample, they are considered the primary client, and the city or town is their client.
We are using the following tables -
tblCustody for storing sample information
tblClient holds all client information for both sities and towns and other labs.
tblClient.PWSNames contains the list of every city, town or other lab that sends samples.
txtLabID is the text field on the form that contains our Lab ID for each sample.
txtPrimary is the field on the form that contains the primary client name.
Our goal is to remove the txtClient text box from the form, leaving only txtPrimary text box containing the primary client name.
When a city or town sends us a sample - our lab name "NEC" goes in txtPrimary.
When we are a sub lab, the lab that sends the sample goes in txtPrimary.
We need to check to see wheter the Public Water Supplier name is the same as that of the Client in the Custody Log.
Both of the following statements are functional:
This is what we want to do:
Remove the txtClient text box from the form and use Dlookup as above.
Check the custody log to see whether the Client name is the same as txtPWSName on the form.
If the Client name is the same as the PWSName the sample came directly to us from a water department and we are the primary. Otherwise the lab that forwarded the sample to us is the primary.
We want to use the DLookup in the same line as the IIf expression. Something like this:
Basically, if the results of the DLookup match the PWSName put "NEC" on the form. If the results of the DLookup do not match, put the results of the DLookup on the form. I know there are syntax errors.
There are a few text boxes on the form we need to get rid of. We hope to figure out how to use IIF with DLookup moving forward.
One last question. We are new to this and coming from a long history of Excel VBA. Hoping to approach things properly.
The form we are creating is going to be printed to a pdf. Should we be creating this form as a report instead rather than a form? The reports will be multiple pages, and we will need to paginate.
Any help is greatly appreciated.
When a city of town send us water directly - they are what we consider the primary client.
When another lab sends us the sample, they are considered the primary client, and the city or town is their client.
We are using the following tables -
tblCustody for storing sample information
tblClient holds all client information for both sities and towns and other labs.
tblClient.PWSNames contains the list of every city, town or other lab that sends samples.
txtLabID is the text field on the form that contains our Lab ID for each sample.
txtPrimary is the field on the form that contains the primary client name.
Our goal is to remove the txtClient text box from the form, leaving only txtPrimary text box containing the primary client name.
When a city or town sends us a sample - our lab name "NEC" goes in txtPrimary.
When we are a sub lab, the lab that sends the sample goes in txtPrimary.
We need to check to see wheter the Public Water Supplier name is the same as that of the Client in the Custody Log.
Both of the following statements are functional:
Code:
=DLookUp("[tblCustody]![Client]","tblCustody","[tblCustody]![LabID] ='" & [txtLabID] & "'") ' Looks up the client name from the custody log - text box txtClient
=IIf([txtPWSName]=[txtClient],"NEC",[txtClient]) ' When the PWS Name is the same as the client name - we are the primary lab. Otherwise it's another lab.This is what we want to do:
Remove the txtClient text box from the form and use Dlookup as above.
Check the custody log to see whether the Client name is the same as txtPWSName on the form.
If the Client name is the same as the PWSName the sample came directly to us from a water department and we are the primary. Otherwise the lab that forwarded the sample to us is the primary.
We want to use the DLookup in the same line as the IIf expression. Something like this:
Code:
=IIF ([txtPWSName]= DLookUp("[tblCustody]![Client]","tblCustody","[tblCustody]![LabID] ='" & [txtLabID] & "'")[txtPWSName], "NEC",DLookUp("[tblCustody]![Client]","tblCustody","[tblCustody]![LabID] ='" & [txtLabID] & "'")Basically, if the results of the DLookup match the PWSName put "NEC" on the form. If the results of the DLookup do not match, put the results of the DLookup on the form. I know there are syntax errors.
There are a few text boxes on the form we need to get rid of. We hope to figure out how to use IIF with DLookup moving forward.
One last question. We are new to this and coming from a long history of Excel VBA. Hoping to approach things properly.
The form we are creating is going to be printed to a pdf. Should we be creating this form as a report instead rather than a form? The reports will be multiple pages, and we will need to paginate.
Any help is greatly appreciated.
