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.
11 1836
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)
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.
Just let me know which direction you're working in when you get stuck and I'll help you further.
Success !
Nic;o)
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.
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)
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
Never Mind
Found it in the VBA Editor
Sorry
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
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
You'll need to add a:
Me.Requery
after the setting of the fieldvalue.
Nic;o)
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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
|
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...
|
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...
|
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...
|
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...
|
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...
|
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"...
|
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...
|
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...
|
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)...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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
|
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...
|
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...
| |