Updating dropdown fields

[cj-1289]

Hi there, I've got myself in a bit of a muddle here.

When you have a field which has a dropdown list of options in the table which you have to use to edit it, I have found that it is not possible to use a recordset to amend it - Access insists that you use the original dropdown menu to select, which is annoying.

Thusfar I've been circumventing it by deleting the troublesome column and moving its data into a normal type column. I then create a dropdown combo box (with manually entered values) in my form which I use to edit the table, which was all I wanted to do at the time and so was completely fine.

Now I desire to utilise the dropdown menu from the table and use it within my form in order to update tables and that is something that I don't know how to do.

Hopefully my question is clear enough. I'd be grateful for any help?

Thanks in advance.

 

[Galaxiom]

The lookup in a form's control is automatically created from the corresponding lookup in the table field.
You can change it in the Row Source property of the combobox control on the form.
You can change it in the table by ammending the Lookup tab of the field's properties.

"Manual" (Value List) Row Sources are rarely appropriate in either situation. You would only use them for a simple combobox that did not involve normalisation of the displayed values.

If you want to change the lookup in the form's controls to accomodate a different Record Source you must use VBA to change the Row Source property of the control to match the new Record Source.

Also, lookups in the tables should only be a temporary feature. Remove them before completing the project.

 

[Scooterbug]

Hi there, I've got myself in a bit of a muddle here.

When you have a field which has a dropdown list of options in the table which you have to use to edit it, I have found that it is not possible to use a recordset to amend it - Access insists that you use the original dropdown menu to select, which is annoying.

Thusfar I've been circumventing it by deleting the troublesome column and moving its data into a normal type column. I then create a dropdown combo box (with manually entered values) in my form which I use to edit the table, which was all I wanted to do at the time and so was completely fine.

Now I desire to utilise the dropdown menu from the table and use it within my form in order to update tables and that is something that I don't know how to do.

Hopefully my question is clear enough. I'd be grateful for any help?

Thanks in advance.

I'm guessing that the "dropdown menu" from the table you are referring to is a table level lookup field. Not a good thing to do. What you want to do is create a lookup table. For example:

tblLookup
lookupID 'Primary key
lookupText 'Data

You can then use the tblLookup as a row source for the combo box, making sure that you store the ID in the parent table. You can then use the lookup table in other areas as well as update the table with new entries when you want to.

 

[cj-1289]

I'm guessing that the "dropdown menu" from the table you are referring to is a table level lookup field. Not a good thing to do. What you want to do is create a lookup table. For example:

tblLookup
lookupID 'Primary key
lookupText 'Data

You can then use the tblLookup as a row source for the combo box, making sure that you store the ID in the parent table. You can then use the lookup table in other areas as well as update the table with new entries when you want to.

Yes, I was referring to a lookup field. I didn't know what they were called at table level because I did not put them there en masse and I would like to strangle the person who did

So, if there are twenty table-level values in a lookup field, your proposal will enable me to retrieve these values by inserting them into a new table which I can then set as a rowsource for a combobox? If so, I can't see how I'm afraid. I can only see how I would create a universally useable rowsource with what you suggest, though perhaps I'm missing something.

 

[Scooterbug]

You will probably have to create the lookup table on your own, as I dont know how to pull the data from the lookup field out. Never used em, so never saw a need to learn about em . As long as the data is in a separate table, the combo box wizard should be able to create the combo box for you. You would have the ID number of the entry stored in the parent table. This is called a foreign key. You can later retreive the data by linking the two fields in a query.

Also, each lookup field would have to have it's own table, unless they the text can be used for different fields. I dont know how many lookup fields you have. You will also need to change the field to a text box under the Lookup tab in the table control.

 

[cj-1289]

You will probably have to create the lookup table on your own, as I dont know how to pull the data from the lookup field out. Never used em, so never saw a need to learn about em . As long as the data is in a separate table, the combo box wizard should be able to create the combo box for you. You would have the ID number of the entry stored in the parent table. This is called a foreign key. You can later retreive the data by linking the two fields in a query.

Also, each lookup field would have to have it's own table, unless they the text can be used for different fields. I dont know how many lookup fields you have. You will also need to change the field to a text box under the Lookup tab in the table control.

There's far too many lookup fields to do that, perhaps 70 per table, with over 11 tables, and perhaps a good fifty values in your average table-level lookup field. I could store it in whatever way, I just need to work out how to get into the table-level values. Browsing around the internet, and in particular looking at
http://www.eggheadcafe.com/conversation.aspx?messageid=33896033&threadid=33896030

I have come to the conclusion that it might just not be possible to get to these values, and I would need to manually create a lookup table containing all these values - perhaps one record to contain each lookup field's values.

But then again, I'd hope that nothing was impossible...

 

[Scooterbug]

Hmmm....was just playing around with trying to create a table lookup field....from what I seen, the row source for a table level lookup has to be an exsisting table, so the data should already be in there. What you need to do is to change it to a text box. Not sure what happens with the data that is already in the field. I would assume that it remains there...but it will be the text, not the ID number.

Like I said, I've never used lookups at the table level...so if i'm off base I appoligize.

Just make sure you make a backup of the database before you start changing things....better safe than sorry

 

[cj-1289]

Hmmm....was just playing around with trying to create a table lookup field....from what I seen, the row source for a table level lookup has to be an exsisting table, so the data should already be in there. What you need to do is to change it to a text box. Not sure what happens with the data that is already in the field. I would assume that it remains there...but it will be the text, not the ID number.

Like I said, I've never used lookups at the table level...so if i'm off base I appoligize.

Just make sure you make a backup of the database before you start changing things....better safe than sorry

Yeah if you refer to my first post, that's the workaround I had been using.

Oh well, I'll just have to suck it up and go on a typing marathon..!

 

[Scooterbug]

Ok...let's see if I can dissect it more.

Your tables have an enormous amount of lookup fields. On a form, you wish to have a combo box in which the user can use to imput data. What are you trying to ammend? The actual data that shows up in the combo box?

 

[Galaxiom]

Hmmm....was just playing around with trying to create a table lookup field....from what I seen, the row source for a table level lookup has to be an exsisting table,

No. You can do a lookup field using a Value List. You can even do a hideous kind of normalisation with the Value List.
With two columns the value list is distributed to column one and two in turn. So a value list might look like 1;textvalue1;2;textvalue2. Enough to give any developer nightmares.

 

[Galaxiom]

If I understand correctly you need to extract the RowSource from the table fields and make them into tables.

With so many tables, so many fields and so many values you need to use VBA.
I don't think there is any way to refer to a RowSource in a table field.
But take heart, it can be done.

Copy your all the offending tables into an empty database.

Make forms with each table as the RecordSource. Use the Wizard and add all the fields. This will create a Value List lookup on each control to match the table fields.

Then extract the RowSource from the controls on these forms.

strRowSource = Me!FormName.FieldName.RowSource
This will return the Row source values separated by semicolons.
Parse this using Split() and append the values to a new table with an autonumbered field.

You will need some nested loops to do this across all forms and fields.

The critical parts in very rough AirCode are:

Dim obj As AccessObject
Dim strCurrentFromName As String
Dim ctrl as Control
Dim strParsedRowSource() As String
 
'loop through Forms
For each obj in Application.CurrentProject.AllForms
   strCurrentFormName = obj.Name
 
   ' loop through controls
   Set ctrl = Control
   For Each ctrl in strCurrentFormName
 
      strCurrentControlName = ctrl.Name
 
' I'm not clear on this bit. How do you return the RowSource property with variables?
 
      strExpression = strCurrentFormName & "(" & strCurrentControlName & ").RowSource"
      strRowSource = strExpression  ' this can't be right
 
      strParsedRowSource = Split(strRowSource,";")
 
      'loop to append the array values to a new table
 
etc etc

Something like this anyway. It will be worth the effort to get it going.
I'm getting out of my depth.
Please others take pity on cj and help them finish what I have started here.
Hope this helps. Good luck.

 

[Galaxiom]

Then you are faced with converting the existing data to the normalised values. I understand that you might want to skip the normalisation but the job won't be done properly.

Make new tables by using a query with Domain Lookups for this step. If your lookup tables are systematically named from the table and field names this should be manageble from a single QueryDef put through a loop.

You need to consider this when generating names for the lookup tables.

If we had a poll for the worst problem on the forum you have my vote.