Combobox help - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Combobox help

Mark Budinger

2 years ago

I was hoping I could get some help. I have a form that I use to inspect machined parts. I use a combobox named "Status" to update the status of the inspected parts. The options are "Open", "Closed", "All Insp.", and "New". There is also a field on the form called [InspectionDate]

If I choose either "All Insp." or "Closed" and the [InspectionDate] is null, I would like a msgbox stating an "Inspection date is required" with just the OK option. If I choose "Open" or "New" the status will change to either of those options regardless if [InspectionDate] is Null or not.

Is this possible? If so, What is the best way to make this work?

-Mark

	Juan C Rivera @Reply 2 years ago
if isnull([name of field]) then msgbox "missing data" end if

	Mark Budinger OP @Reply 2 years ago
That was the first thing I tried. I only want the msgbox if the status was trying to be changed to Closed or All Insp. and the [InspectionDate] is Null. Is this possible? Regards, Mark

	Juan C Rivera @Reply 2 years ago
look at the event on change if isnull([inspectiondate]) and ([nameoffield]) = "Closed" or ([nameof field]) ="All Insp." then msgbox end if try that if not correct play around and see what you come up with. Let me know V/r Juan

	James Carr @Reply 2 years ago
Mark did you get your form working? I have a similar form with code on the save button in case this helps. `Private Sub cmdSave_Click() If IsNull([inspecdate]) And inspectstatus.Value = "Closed" Or IsNull([inspecdate]) And inspectstatus.Value = "All Insp" Then MsgBox "Inspect date is a required field", vbOKOnly Else DoCmd.Save MsgBox "Successful", vbInformation End If End Sub`

	Mark Budinger OP @Reply 2 years ago
Juan, Your code worked perfectly except once I press the OK I would like the Status to go back to what it was set to before trying to make the change. What code would I use for that? James, Thank you for your reply, I haven't tried you code yet. I will try it and see what results it gives me Regards, Mark

	Juan C Rivera @Reply 2 years ago
I say try adding the else if () then else cancel = true ' if cancel dont work try the exit sub. end if V/r Juan

Mark Budinger OP @Reply

2 years ago

Hi Juan,

I did my best to try your solution but I ended up with the same results.

This is the code that I am currently using which works but doesn't revert back to original state after the Ok is pressed.

If IsNull([InspectionDate]) And ([status]) = "Closed" Or ([status]) = "All Insp." Then
MsgBox "Inspection Date is missing", vbOKOnly + vbInformation, "Inspection Date is Required"
End If

I am still learning my way around IF statements.

Regards,
Mark

	Mark Budinger OP @Reply 2 years ago
Hi Juan, I think I resolved my issue. On the line after the MsgBox I just added Undo. My new code If IsNull([InspectionDate]) And ([status]) = "Closed" Or ([status]) = "All Insp." Then MsgBox "Inspection Date is missing", vbOKOnly + vbInformation, "Inspection Date is Required" Undo End If This seems to do the trick. Regards, Mark

Adam Schwanz @Reply

2 years ago

The only downside to that solution is that undo will undo everything that has been changed since the last time the record was saved, which could be other fields/data as well. That said, I don't know if there's a good way to do it to only affect one field, i guess you could use like tempvars to store the previous variable, check the status, and then revert it back to the tempvars if it didn't match.

	Mark Budinger OP @Reply 2 years ago
You are right Adam. I just tested my form and changed a few things then tried to change the status and when the undo happened, it removed all my other data. I was hoping for a quick and easy solution to this. I will look into tempvars.

	Sami Shamma @Reply 2 years ago
Did you say Tempvars? oh you have made Adam very happy.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View