473,322 Members | 1,714 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Limiting Selections for Values in a Form or Table

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.
Feb 2 '07 #1
11 1836
nico5038
3,080 Expert 2GB
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)
Feb 2 '07 #2
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.
Feb 2 '07 #3
nico5038
3,080 Expert 2GB
Just let me know which direction you're working in when you get stuck and I'll help you further.

Success !

Nic;o)
Feb 3 '07 #4
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.
Feb 4 '07 #5
nico5038
3,080 Expert 2GB
Just base your subform on a query e.g. named like qrySubform

Now in code you can construct the query like:

Expand|Select|Wrap|Line Numbers
  1. dim qd as DAO.querydef  ' This does require Tols/Reference set to Microsoft DAO version #.##
  2. ' link the qd to the query
  3. set qd = currentdb.querydefs("qrySubform")
  4. ' now fill the SQL in the query
  5. qd.SQL = "select x from tblY where ID=" & me.comboID
  6. ' refresh the subform
  7. me.subformname.form.requery
Nic;o)
Feb 4 '07 #6
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
Feb 8 '07 #7
Never Mind

Found it in the VBA Editor

Sorry
Feb 8 '07 #8
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.QueryDefs("qrySelection")
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
Feb 9 '07 #9
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
Feb 9 '07 #10
nico5038
3,080 Expert 2GB
You'll need to add a:
Me.Requery
after the setting of the fieldvalue.

Nic;o)
Feb 9 '07 #11
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.
Feb 9 '07 #12

Sign in to post your reply or Sign up for a free account.

Similar topics

6
by: Kingdom | last post by:
I'm using this script to dynamicaly populate 2 dropdowns and dispaly the results. Choose a component type from the first drop down, lets say 'car' and the second box will list all the car...
18
by: Neil | last post by:
I am using SQL 7 with an MS Access 2000 MDB front end, using bound forms with ODBC linked tables. In one form, the user needs to be able to check a box to select one or more records. This is...
2
by: Kathy | last post by:
Scenario: Multiple Divisions. Each Division has multiple Buildings. Each Building has multiple Floors. Each Floor has a Supervisor. Tables: (abbreviated) TblDivision DivisionID Division
6
by: Shannan Casteel via AccessMonster.com | last post by:
I'll explain as well as possible...not an experienced user...but learning. I am working on a database for different kinds of problems with machines. A user will be able to select a problem...
18
by: Alpha | last post by:
Hi, I'm working on a Windows applicaton with VS 2003 on windows 2000. I have a listbox that I have binded to a dataset table, "source" which has 3 columns. I would like to display 2 of those...
1
by: Patrick | last post by:
Hello - I am designing a form that only allows 2 checkboxes to be checked out of a series of checkboxes. However, checkedchanged and checkstatechanged both act when the box is clicked on. I want...
7
by: callawayglfr | last post by:
I am building a database in access where I have a drop down box that relates to a text box, that part I have working but when someone selects information from the first drop down I need it to limit...
5
by: EricS | last post by:
Hi, Was wondering if anyone can help. I have multiple queries in a database. The selection criteria for these queries is always a time period I want to do the analysis by. Rather than having to go...
3
by: andersond | last post by:
I have a webpage that uses visible and hidden tables to create the effect of going from screen to screen. Because a user might want to go back and change previous selections I have a "previous page"...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.