update field value based on value from another field

[WatsonDyar]

I have a form used to track attendance of employees:

I want to update a value in Field B to a default value if a specific criteria is selected in Field A. How? I cannot seem to find a answer...

Specifically:
If the combo box value "Not Present" is selected from Field A, I want to value in Field B to automatically update to "Not Available".

Any ideas?

 

[GinaWhipp]

Hmm, something like...

If Me.ComboBoxA = "Not Present" Then
   Me.FieldA = "Not Available"
End If

...in the After_Update event of ComboBoxA. Though you probably want to add an *Else* part for if it's not equal to "Not Present".

 

[WatsonDyar]

Worked perfectly and I learned something- thanks GinaWhipp!!

 

[GinaWhipp]

You're welcome!

 

[WatsonDyar]

Got another question:

Is it possible to limit the value options in "Field B" using a similar code method?

Specifically:

Field B is a combo with the options of : 100% Availability, 75% Availability, 50% Availability, 25% Availability, OR Not Available

If I choose "Present" in Field A, I want the "Not Available" value to disappear as an option.

 

[GinaWhipp]

Kind of guessing here because you didn't give me much to go on. Something like...

 If Me.MyField = "" Then
   Me.Field = 0
End If

 

[WatsonDyar]

I tried that on the Load event. Not success.

Here's more details:

I need the following to display a zero if a null value is returned:

SELECT tdsIndivData.dsReportID, Count(tdsIndivData.StaffID) AS CountOfStaffID
FROM tdsReportData INNER JOIN tdsIndivData ON tdsReportData.dsReportID = tdsIndivData.dsReportID
WHERE (((tdsIndivData.Availability)="75% Availability"))
GROUP BY tdsIndivData.dsReportID;

 

[GinaWhipp]

Oh, well no that won't work with what I posted, try...

SELECT tdsIndivData.dsReportID, Nz(Count(tdsIndivData.StaffID),0) AS CountOfStaffID
FROM tdsReportData INNER JOIN tdsIndivData ON tdsReportData.dsReportID = tdsIndivData.dsReportID
WHERE (((tdsIndivData.Availability)="75% Availability"))
GROUP BY tdsIndivData.dsReportID;

 

[GinaWhipp]

Great, will be looking at shortly!

 

[GinaWhipp]

Okay, I now know why it's not showing anything, it's because there is no data in the query itself that shows so it can't show zero or anything else for that matter. If a query returns no results that is not the same as NULL or EMPTY. So, we have to take another approach.

Take a look at the ones I did make the zero show for and you'll see what I did.

 

[WatsonDyar]

I see. So, you used the Nz function in the subreport field instead of the query.

Thank you so, so much!!! I've learned a bunch from you over the past day or so!

 

[GinaWhipp]

Correct, glad to help!