Combobox Not Lose Data?

[darkstar257]

I'm saving links to a combobox in a form, but each time I close out of the form and reopen it the Combobox doesn't keep the list of data I entered before and only keeps the last selected one in its drop down menu.

Is there a way to make the Combobox store/keep the data?

 

[boblarson]

Use a table to store the list items and then set the combo's Row Source Type to Table/Query and select the table that you have stored the items in as the Row Source.

 

[darkstar257]

What command would I use to append items to the combo box now with this method? combobox1.AddItem no longer works.

 

[boblarson]

Use the not in list event of the combo and if you are on 2007 or 2010 you just need to create a form bound to the table I told you to create and then you save that and then in the combo's List Items Edit Form property you just select that form. No code or anything needed.

A related thread is here:
http://www.access-programmers.co.uk/forums/showthread.php?t=216871

 

[darkstar257]

I am using Access 2003

Also using this method, causes the combobox to pull out the same table/query list each time. I'm trying to get each form's combobox to pull up its own list. Is there away to do this using One-to-Many relationships?

 

[jdraw]

How about telling us WHAT you are trying to do.

What exactly does this mean

I'm trying to get each form's combobox to pull up its own list.

?
Please give us some sample data.

 

[darkstar257]

I am trying to be able to simulate the ability to attach documents onto each form of the database by storing Links to files inside a combo box.

However ...

• I can't make the combobox store the links after one closes and reopens the form
• Sourcing the combobox to a table/query causes the combobox to pull up the entire table (DocFields) despite having One-To-Many relationship based on IDs

Attached is an example of what I am making

 

[jdraw]

I looked at your database. I'm confused. Not sure what you are trying to do.

I think you may need a junction table to identify those documents selected for/by Manufacturer.

You may have Manufacturers (tblTest) [ a poor Table name in my view]
And some documents.

Many manufacturers, many Documents is M:M

If you want to identify those Documents related to/assigned to a Manufacturer
then use a junction Table

ManufacturerUsesDocuments

Id autonumber
DocFieldId Long Integer
tblTestId Long Integer

create a unique composite index on DocFieldId and tblTestId to prevent duplicates

If I have misunderstood, then please clarify.

Also, Forms do not store data in and of themselves.
Forms are a means to display/present records from a Table(s)/Query(s)

 

[darkstar257]

Maybe this clears things up more. Issue is that the combobox pulls up the entire DocField table instead of the entries with IDs that are linked to the form's ID.

 

[boblarson]

Maybe this clears things up more. Issue is that the combobox pulls up the entire DocField table instead of the entries with IDs that are linked to the form's ID.

Your combo box's row source query is missing any where clause. Replace that query's SQL with this:

SELECT DocField.SelectedDocument
FROM DocField
WHERE (((DocField.ID)=[Forms]![frmTest]![ID]));

 

[darkstar257]

Your combo box's row source query is missing any where clause. Replace that query's SQL with this:

SELECT DocField.SelectedDocument
FROM DocField
WHERE (((DocField.ID)=[Forms]![frmTest]![ID]));

Thanks this is what I was looking for. But it still doesn't work properly. It doesn't seem like the SQL statement is taking effect at all.

 

[boblarson]

Thanks this is what I was looking for. But it still doesn't work properly. It doesn't seem like the SQL statement is taking effect at all.

In the form's On Current event add

Me.ComboBoxNameHere.Requery

 

[darkstar257]

In the form's On Current event add

Me.ComboBoxNameHere.Requery

Already tried that there. It's still pulling up the entire DocFields table instead of just selecting the matching IDs for some reason.

 

[boblarson]

Already tried that there. It's still pulling up the entire DocFields table instead of just selecting the matching IDs for some reason.

It worked for me just fine in your sample when I did it.

 

[darkstar257]

It worked for me just fine in your sample when I did it.

It's working for me now too. Some reason the file bugged out on me.

I found a new problem now. It seems that I can't assign the main form's ID number to the DocField table as the form's ID is still on "Autonumber" or null value when I make a new record. Is there a way around this?

You tried to assign the Null value to a variable that is not a Variant data type.

It has to do with the .Fields("ID") = Me.ID part.

Set db = CurrentDb
    Set rs = db.OpenRecordset("DocField")
...
With rs
            .AddNew
            MsgBox (.Fields("ID"))
            [COLOR="Red"].Fields("ID") = Me.ID[/COLOR] [COLOR="SeaGreen"]'Form ID is still null for new records[/COLOR]
            .Fields("SelectedDocument") = BrwsFl.SelectedItems(1)
            .Update
            .Close
            Me.SelectedDocument.Requery
            MsgBox ("Added " & BrwsFl.SelectedItems(1))
            
        End With
...