Access / VBA Code Question

Question

Good afternoon, I am looking for some help with writing vba for the following scenario. I have a table (tblJobs) that has the following fields: jobid, jobName, jobDescription, and Disclosure. Disclosure is a Yes/No field. I have a form which contains a list box (lstJobs) and the list box is populated with the information from the tblJobs. Once the form is opened, a user can select one or more of the jobs from the lstJobs listbox. What I am looking to do is this: if the user selects one of the jobs where the disclosure field = Yes, once they hit submit, a popup form opens with some verbiage about the job. The user then would click ok the jobs are submitted. I only want this pop up form to open if any of the jobs they selected has the disclosure set to yes. If they select a job or jobs with a No, the popup doesn't open.

Here is  what I have thus far.

Dim frm As Form

 Dim ctl As Control

 Dim varItem As Variant

 Dim strSQL As String

 Dim answer As VbMsgBoxResult

 Set frm = Forms!frm_SelectJobs

 Set ctl = Forms!frm_SelectJobs!lst_Jobs

answer = MsgBox("Just a final check before we log you on the bid sheet? Are you ready to submit your selections", vbYesNo)

If answer = vbYes Then

For Each varItem In ctl.ItemsSelected

 'Review for disclosure selection & open disclosure box if applicable

 strSQL = "INSERT INTO tbl_SelectedJobs (Employee_ID, Job_ID) VALUES(" & _

 Me.Text2 & ", " & ctl.ItemData(varItem) & ");"

 CurrentDb.Execute strSQL, dbFailOnError

 Next varItem

Any guidance would be great. Thanks in advance.

Answer 1

Hi Kimberley,

This thread https://www.pcreview.co.uk/threads/vba-loop-thr... Should help you with this. Let me know if you need more.

Please Note: This is a non-Microsoft website. The page appears to be providing accurate, safe information. Watch out for ads on the site that may advertise products frequently classified as a PUP (Potentially Unwanted Products). Thoroughly research any product advertised on the site before you decide to download and install it.

Answer 2

Yes, it's set to allow multiple selections. Can you provide any direction on how I'd look through and test?

Answer 3

Is your list box set to allow Multiple selections? If not, you can use the Column property to check the Disclosure column.

If Me.lstbox.Column(3) then

Disclosure message

End if

If it is set for multiple selections. Then you have to look through the ItemsSelected collection and test the Disclosure column.