Hi. I am new to VB(A) but willing t

[PcView]

Hi. I am new to VB(A) but willing to learn fast (and to make time for it,...) so here is my "very little" problem. I have made a Form in Access2000 and created 2 listboxes on it (both multiselect). The result of those selections should act like an SQL-statement or query to be used on a Report. I made a Botton on the Form to trigger the Report. This is where VBA or VB comes in and that's why I am new on this forum,... .
The listboxes are filled with the domains of the tables Areas (Stock A - Stock B - Stock Central - All Stocks) and Types (Workstation - Keyboard - Screen - Printer - ....).

The goal is to select for example Screens and Keyboards from Stock A and Stock B and put them in a Report I already created before (so I have a clean output from the inventory). The problem at the moment is that I can't find the right way to "read" the selection in the boxes and use them as the Query or SQL-statement for the Report.

Please don't solve the problem completly, all hints in the good direction would help me solving the problem are more than welcome (and I learn the most out of it).

Thanks.

 

[Jucius]

A note about reading selection boxes:

Even though your selection boxes are full of text labells, all access cares about is the primary key attached to those options.

So if you select "Some Option" which has a primary key of 59 in a text box called "MyTextBox1" and then try to access the control called "MyTextBox1" you'll get the value 59, not "Some Option" .

If this is a problem, I suggest you look up the DLookup function.

Now this should help you detect what's in your text boxes.

To execute the SQL, you could do some VBA like:

CurrentDB.Execute "Select " & somevariable & " from " & othervariable & " where ...

and so on

I hope this helps you.

 

[PcView]

Thanks for the information, it helped a lot. Everything works smootly.

Still a remark :

'Check if input in listbox Areas
Dim a As Integer
Dim NoneSelected As Boolean
NoneSelected = True
For a = 0 To Forms!reportMenuForm!ListAreas.ListCount - 1
If Forms!reportMenuForm!ListAreas.Selected(a) Then
NoneSelected = False
End If
Next a
If NoneSelected = True Then
MsgBox "Please Select 1 or more Areas", vbExclamation, "No Areas selected in list box"
Forms!reportMenuForm!ListAreas.SetFocus
End If

I used this code to check the input of the listbox, and it does,..., the program continues with the next statement. What I mean is I want the program to stop until the user selects at least 1 item in the listbox.

All tips and tricks welcome. I'll keep searching and reading this great resource for a newbie addict like myself!

Greetz

 

[Jucius]

After detecting the error condition, you might want to add an

exit sub

so that the code after the stuff you have above is avoided.

 

[PcView]

Hi

Thanks, saw it right away when I read you post.

Exit Sub
"Immediately exits the Sub procedure in which it appears. Execution continues with the statement following the statement that called the Sub procedure."

Another lesson in life ;-)

Cheers