Limiting Selections for Values in a Form or Table

[WilDel97]

Hi,

I've been coming to this forum for a couple of weeks looking for a solution to a problem I'm having, and don't know if there is an easy solution. I've worked with Access for a while, but only the basics, and I know very little about VBA, but I am trying to teach myself, so pardon me if this makes little sense.

I am writing a truck dispatch program, and I am trying to limit selections in a table/form based on earlier entries in the record.

The fields are Project (Text), Work Date (Date/Time), VendorID (Text),
TruckTyp (Text), TruckNo (Text).

The TuckTyp, VendorID, and TruckNo all references back to seperate lookup tables. However, they are all joined by relationships. The TruckTyp and TruckNo are in a table with the VendorID.

What I am trying to do have the truck type limit its choices based upon the Vendor ID. And then have the TruckNo limit itself based on the VendorID and the TruckTyp.

I have gotten close by manipulating the lookup criteria, but I can't seem to find a way to pass the VendorID to the TruckTyp search, or when I do it takes the first value entered and holds it throughout the whole table, unless you shut down and restart the application.

Thanks in advace for your help.

[nico5038]

This problem can be solved in a number of ways.
Personally I prefer to instruct my users to use the right-click popup menu.
This will allow them to filter and sort a datasheet subform as they like and saves me the effort of writing code.

The other option is to use cascading comboboxes where the source of the "lower" combobox has a WHERE referring to the name of the form control of the higher combo.

Finally you could write code to build the filter for the datasheet subform yourself.

Nic;o)

[WilDel97]

Thank You.

I had been working along those lines, but it never worked quite right. But now that I know I am working in the right direction, I can get it figured out.

[nico5038]

Just let me know which direction you're working in when you get stuck and I'll help you further.

Success !

Nic;o)

[WilDel97]

Thank You

I appreciate the point in the right direction. I set it up with the WHERE clause, and got it do what I wanted. However, it is running on a continuous form, so it would not requery when I went to a new record.

I thought about leaving a post and asking for more help, but I also need to learn this stuff for myself. So, I dug a little deeper in the forums and found what I needed, some code to run behind the form (My first VBA ever). This did not run perfect, but with a couple of tweaks, I think it will work. I'm not sure it is the proper way to do it, but it will work for what I need.

Once again I appreciate the response. So many times with this stuff, I know the answer is out there, and I just need that push that tells me I am headed in the right direction.

[nico5038]

Just base your subform on a query e.g. named like qrySubform

Now in code you can construct the query like:

dim qd as DAO.querydef  ' This does require Tols/Reference set to Microsoft DAO version #.##
' link the qd to the query
set qd = currentdb.querydefs("qrySubform")
' now fill the SQL in the query
qd.SQL = "select x from tblY where ID=" & me.comboID
' refresh the subform
me.subformname.form.requery

Nic;o)

[WilDel97]

Been working through some other options, but went in to try and set up what you had shown. Having trouble.

When I run it, I get a Compile Error - User Defined Type not Defined.

I guess I didn't understand where the Tols/Reference had to be set

Thanks

[WilDel97]

Never Mind

Found it in the VBA Editor

Sorry

[WilDel97]

Sorry to bother again, but I got the upate to work like you suggested, but the value I am passing from my list box is a text field with a zero in the front

Value = 072001 (that is the way we assign project numbers)

Year (2 Digits) - Company (1 Digit) - Job Number (3 Digits)

This is something I cannot change, as it is how we track everything in our accounting.

But when it gets into the query, it shows up as 72001, which doesn't work for the sort.

I tried setting the value up as a string:

Dim sqlProjectNo as String

Set qd = CurrentDb.Query Defs("qrySelect ion")
sqlProjectNo = lstProjectNo
qd.SQL = "Select String =" & sqlProjectNo (String is very long didn't post it)
me.form.requery (no Sub Form)

and it still passes it back to the query as 72001

Is there any way to keep that leading zero.

I also tried it by setting up a project 120001, but this does not work either since it is passed to the query without the quotes "120001" works, but 120001 does not.

Any help would be appreciated.

Thanks

[WilDel97]

I tried changing a few things:

I changed the text format to a number and everything works, except, when I select a project in the list box it passes the right value to the query but the form doesn't update.

I am doing the list box as an unbound list box on the same form.

The form is based on the query I am updating.

If I close the form and re-open it after selecting the value it shows the correct values for the query. It just doesn't do it right away.

I have the action set to After Update of the list box.

Thanks

[nico5038]

You'll need to add a:
Me.Requery
after the setting of the fieldvalue.

Nic;o)

[WilDel97]

What do you mean by field value? I've placed it after the list box where I select the new value, but no good results. I tried making a subform, but no luck.

It works if I select the value, go into the design view, and when I come back out of the design view it has the new query, but it doesn't refresh while I am on that page.

At this point, I am thinking of leaving that option out of the program for now, and maybe put it in a later version of the program after I have had more time to learn the in's and out's of VBA.

Thanks for all the help. I'll be refering back to this later.