Tech Support Guy banner
Status
Not open for further replies.

Solved: Access Forms

3K views 31 replies 7 participants last post by  jimr381 
#1 ·
Hi,

I need a bit of help as I have no idea how to go about even starting this. I would like to have a drop down box (which is the part I can do), but then depending on what was selected from that box, I would like to have another drop down box appear if a certain something was picked from the first. Is it possible to do this in just SQL? Can somebody point me in the right direction? ...OBP?:)
 
#2 ·
It can be done with VBA and SQL, but I personally prefer to use a Query as you can test it before finalising the Form.
I have previously posted a few examples of how to do it, so if you run an advanced search for User - OBP you should find them.
If you don't come back on here and I will post it again.
 
#4 ·
Hi OBP,

Thanks for the reply. I had started looking and had not found what I was looking for.. The problem is, mine, I think, is a bit more complicated.
I have a pull down box that picks a Department number. Each department number has a kstID. Some of the departments can be a parent, in other words, their kstID shows up in some department number's parentID.

The first box picks the department number and if it doesn't have it's kstID ind any of the parentsID then I can do nothing. But if the department number's kstID is in a parentID, then I want to display them in the 2nd combobox.
Ugh, no wonder I didn't become a database person!
 
#5 ·
Ugh, no wonder I didn't become a database person!
Well, you are on your way now!
OBP, I look for you on this, but wouldn't it be easiest to have a child field of "" or N/A or none or something like that - so that all the departments have some child field?
 
#7 ·
Oh and the part that is really confusing me is this:

The parentID is defined? in the same table. In other words the parentID number should match the kstID and they are in the same table! I can't work around that! And I can't post my DB, it's too big :(
 
#9 ·
Make a copy of your db, and delete a bunch of the info in it, then zip it and post it. I had one the other day that was too large to post until I deleted a bunch of information (most of the employee info, most of the states, nearly all of the counties in those states, etc.).
 
#11 ·
It's possible. You need to have tables with a relationship to one another first. Sale like a manufacturer table and a model table.
example
Manufacturer table:
man_ID....man_Name

model table:
mod_ID......man_ID.....mod_Name

Now you can build the combo box for the manufacturer with all columns and another combo for the models with all of the fields and filter set to the man_ID. Set the onChange event for the first combo to requery the model combo.
 
#13 ·
not quite, but I might have figured it out...I will write up the code tomorrow and if it works or doesn't work, let you know :) I'll also look at yours more tomorrow, although quickly looking at it, I have to have the user pick from department ID so I think if I can insert the value into a table it might work to store it and use it then in the next drop down. I will try tomorrow as it's late and I just had french class so my brain is fuzzy...

Talk to you tomorrow!! :)
 
#14 ·
Oh and the part that is really confusing me is this:

The parentID is defined? in the same table. In other words the parentID number should match the kstID and they are in the same table! I can't work around that!
This sounds like an enormous problem to me. I won't distract from the direction of the thread because it sounds like you're close to a solution. But just based on what I've read (since I can't look at the examples), I would guess that the entirety of your difficulty rests here. Your requirement is very common and relatively simple to do otherwise.

Good luck :up:

chris.
 
#15 ·
It can be done with VBA and SQL, but I personally prefer to use a Query as you can test it before finalising the Form.
On the technical side, this statement doesn't make sense to me? A query is just a SQL string, and to do this with VBA you use a SQL string, so you can test both methods before even creating the form if you like, can you not?

chris.

[edit]
Or, if you mean you can test the controls before the form is saved, the same is true for the VBA solution. In my mind the only difference is that the VBA method requires more effort in return for far greater control and robustness, whereas the saved query method is much simpler to implement because it only addresses the most commom use.
[/edit]
 
#16 ·
Good Morning all,

Well, I tried my way and I tried using OBP's way. Is there a way to combine 2 select queries into 1?

Here's what I want to do:

SELECT ctrl_kst.kst_id
WHERE forms!Pick_kostenstelle_form!Pickkostcb = ctrl_kst.kst_nr

Then I want to run this at the same time

SELECT ctrl_kst.kst_nr
WHERE ctrl_kst.kst_parent_id = (the kst.kst_id from above)

And then display these results in my second drop down box.

OBP is close, but for some reason it picks from the wrong numbers in the beginning. The people are choosing from kst_nr at first.

Does that make sense?
 
#17 ·
In the example that you provided the Parent-ID never equals the kst_nr so it is not possible to test it.
I have changed/added some numbers to the Parent_id to see if this is what you want.
What I do not understand from your last post and the data provided is what you are trying to achieve as there is only one instance of each Proffesur_name to select, (Which is why I chose the Parent_id as it has more than one entry with the same number), so why do you need the second Combo?
What does it do?

Perhaps if you could explain what you were trying to achieve in terms of the Data rather than the field names I might be able to grasp it.
 

Attachments

#18 ·
Hi OBP,

Is that the wrong database?

Ok, I'll try and explain it...

We have departments and professors and each of them has a costcenter. Although some cost centers can have multiple professors and cost centers in them. So I want the user to be able to pick a cost center from them all and then if they pick a cost center that can have multiple cost centers in it, I want the second box to be filled with the original picked and the other cost centers that can be in it.

The parent ID in this case points to the cost center ID, which is the ID to get the cost center number. I know, very confusing. My problem with this whole thing is I didn't create the tables or relationships or anything else, other than I have to make this stupid drop down box and a report. I had a report all set and then my boss said that he hadn't realized this and that and boom! I'm back at the beginning! I'm 2nd level support, not a DB person!!!
 
#20 ·
jpalmerb, yes it is an old version, but it has got a modified form and data as described in my post, but I do not think it is what you want.
Can you you repost it with some representative data in it i.e. more than one cost centre with multiple cost centres in it?
Most of the Parent_id were balnk in the version that you originally posted, so that is not much to go on.
How do you show a "Cost Centre" is within another "Cost Centre" in the table(s)?
 
#21 ·
That's the confusing part.

Ok, person picks kst_nr, which has a kst_id. and a kst_parent_id. From the kst_nr picked, get kst_id, then search in kst_parent_id for the same number. The kst_nr from the results should be displayed in the second box.
I'm sorry this is so confusing.
I'm attaching my latest DB

The form Pick_kostenstelle_form is the one I am working with. Which uses ListKostenstelle Query for the first drop down box (which works) and ListProf Query for the second one, which isn't quite there.

The table is ctrl_kst. I found when I look at that I can understand what I needed to do :) Even that took a while for me! Because the kst_parent_id is backwards of what I would have done. It acts like a pointer to kst_id to which it is a child of/the second box part :)
 

Attachments

#23 ·
Hi OBP,

Nope , I don't get anything in the 2nd drop down box. In my newest version that I've been playing with I somehow get all of the cost centers that are part of another one in the drop down box. And I just tried running a macro with SQL code to get from the cost center to the ID and from the ID to the parent and back to the cost center. It's like running in freakin circles!!!!
 
Status
Not open for further replies.
You have insufficient privileges to reply here.
Top