onLoad: display stored value in a combobox filled by SQL

[ezechiel]

hi,

the combobox is filled correctly and when I choose an item, the selected item is put into the DB by _AfterUpdate code.

Row source:

SELECT SOP.SOP_id, SOP.SOP_name, SOP.SOP_nr
FROM SOP 
WHERE SOP_id = 1 OR ((SOP_domain IN  ("soft","all"))
AND (SOP_type IN ("U", "UM")))

SOP=procedure
So, I have a form to edit software records and there are three comboboxes:
- list of procedures for using the software
- administrate sw
- install sw

When a SOP is selected (afterupdate), the SOP_id is stored in another table with primary key the software_id.

So, everything works perfect except this:
When I choose a software to edit, the combobox is empty (not the dropdown list. Tell me if not clear, please).

What I would like to have is that when I open the form (swedit) is that it displays (in the cb_box) SOP_id, SOP_name and SOP_nr from SOP table, depending on the SOP_id and current software_id stored in the SOFTWARE table.

In SQL it would give this:

SELECT sop.sop_id, sop.sop_name, sop.sop_nr
FROM sop, software
WHERE sop.sop_id = software.sop_use
AND software_id = " & Me.software_id.Value & "
 

Me.software_id.Value holds the software_id of the current record.

First, is it "Private Sub Form_onLoad()" or "Private Sub Form_Load()"?
Next, I've seen that I have several choices:
- dlookup
- .SelectedIndex
- other possibilities??

I admit that I'm a bit lost here. Thanks for the upcoming help!

[ChipR]

Form_Load is the name of the sub for the On Load event. If you want to look up a value from another table to display on your form, the easiest way would be DLookup. However, if you want several values from the same record, it may be slightly faster to open a recordset and move to the correct record where you can grab the values, rather than do multiple DLookups.

[ezechiel]

Form_Load is the name of the sub for the On Load event.

What should I put then to trigger an action when the form loads if form_load is just the name. Sorry, I'm not english and that sentence is confusing me..

well, the form is actually the record of the chosen software, where you can edit any information (serial number, version, etc..) . Another form is available for browsing only (but this doesn't changes the problem). The other fields don't cause any trouble since the lookup is automatic when opening the record (due to control source i suppose?)

the combobox is an unbound one (control source empty). The problem is that the record is for software and most of information comes from the software table filled in automaticilly by access. These comboboxes are not. The info to display comes from another table, based on the current record info. And they are "unbound" because I can't get the wanted result with 'record source'.

[ChipR]

Does this form allow you to change records? If you just want to do this when the form opens, use "Private Sub Form_Open" and if you want to run the code every time a record is selected, instead use "Private Sub Form_Current." You can always go to the form properties in design view, event tab, click the elipsis, and select "code builder" and have it create the procedure for you.

I can't figure out whether you have one combo box or multiple ones, whether they have a RowSource set or not, or what they are supposed to look up based on what foreign key from your current form.

[ezechiel]

The row source is in the first post ;)

The three comboboxes do exactly the same, except that there is a difference in the row source query in the WHERE part. So if I got the solution for one, I'll just have to adapt for the others.

fk, also see first post: WHERE sop.sop_id = software.sop_use
So sop_use is the fk in SOFTWARE table for sop_id in SOP table.
Software records are based on the SOFTWARE table (all items, except comboboxes have record source in software table)

So when the form opens (or loads), the software_id is known, and the dropdown list of combo is filled with row source query. But the "display" of the combobox is empty. Actually, it should look up the number in SOFTWARE.sop_use and get the corresponding info from the SOP table and display it in the combobox, like the 2nd query in post1.

I hope it is clear for you now ^^ (the source of the problem is that I didn't build the application, the DB is not ACID at all and I must improve it. In clear, make gold with lead.. not easy)

I'll give the code builder a try, but I really hate that thing (or is it Access? Lord praise non-access dbs!)

[ChipR]

Assuming your combo box is bound on the first column (and software_id is numeric not text), I think you want to do:

cmbBox = DLookup("sop_use", "software", "[software_id] = " & software_id)

[ezechiel]

cbbox is unbound.. Should I bind it with first column of SOP table?
NB; software_id is not present in SOP table..
Binding the combobox with field in software

WAAAAAAAAAAAAA sorry for all this mess..
So, I bound the combobox with sop_use and now it works...
The strange thing is that I tried it before but it wasn't working. Well, the thing is so badly build that it bugs now and then, this isn't the first time that things don't work and next day they work, without having changed anything.... But hell, what an amount of time did I lost because of this!!! It's driving me nuts...

Thanks for the help anyway ;) So after all, don't need dlookup anymore but the solution you gave above must be working I guess. Why is access such a relief and a PITA at the same time???

[ChipR]

Well, glad you got it working.

[ezechiel]

Euhm, this works with a combobox, but not with a textbox (browsing form, non editable. It displays just the id stored in sop_use. Any ideas (because there is no row_source..).

A solution would be a non editeable combobox, but graphically less nice since there is nothing to "drop down"..

[ChipR]

Sometimes I put a combo box on my form that isn't visible, use it to look up the value, and then copy the value in whatever column to a visible text box. Like txtBox = cmbBox.column(1)

[ezechiel]

oh, nice hint, thanks.
Or it could also just work by coding it?
run a query and put the result in the textbox?

[ChipR]

A query doesn't really give you an answer, it gives you a recordset. So you would either open a recordset based on a query, or use DLookup for simple cases, or use the hidden combo. Either of those solutions will require a small amount of code.

[ezechiel]

ok, thanks. I think I'll try dlookup and if I don't manage to get it work properly,
I'll use the hidden combo.

This topic may be closed. Thanks again.