Transferring Data between Forms

[ControlHead]

Hello All,

I have a tricky problem. I have two forms in MS Access where the user enters information.

The first form links to a table with only one record where data is entered via text boxes. Lets say the user enters "Red", "Blue", "Green" and "Yellow". No problem here.

Now on the second form, the user needs to select some of the information previously entered on form 1 by means of drop-down combo boxes, where these selections from part of seperate records in another table. Lets say on this form he can enter "Cars" and then make a colour selection for record 1. For record 2, he types "Boats" and then makes another colour selection.

I need to find a way of populating the combobox either with a suitable query linked to the combobox record source, a query in VB coding to add to the combobox or maybe by populating a third table that links to the combobox row source. I've tried all manner of tricks and cannot get the (horizontal) single record data into a (vertical) multiple record format.

Anyone with any ideas? Thanks!!

[MMcCarthy]

You have posted this question in the Articles section. I am moving it to the Access forum.

ADMIN

[MMcCarthy]

Sorry if this sounds glib but it shouldn't be a single string record from what you say. Why have you entered it that way? If you then select it as a foreign key on another record it won't work.

[ControlHead]

My appologies for the posting to Articles - I only saw it once it was too late.

There are basically three reasons why I'm forced to enter the data into one record in Table A:

1) That single record relates to information for only one (quotation) project at a time. If the user wants to work on another project, he opens a new database.

2) Entering the data as multiple records would work fine and would enable me to source the information for the ComboBoxes on Form B, but I have no control over how the information is shown on the form, because the Form Design View only shows the one, common record link and I need to re-arrange all the Text Boxes to suit my required layout. Unfortunately, they're not in order from top-to-botton, left-to-right. I am able to do this with a single record because all Text Boxes are available in Design View. Maybe there is a way to custom-place the fields for multiple records?

3) The limitation of only being able to link one table to a form. If it would have been possible to link a second Table to Form A, then the data required for Form B would be available in its own table.

I'm sorry it sounds so complicated, but it's not really. I'm trying to achieve a simple objective, but the way Access does things is complicating the issue. Maybe I could e-mail you a screen dump so that you could see what I'm trying to achieve?

[ControlHead]

.............................yawn................. ....................

[JConsulting]

.............................yawn................. ....................

yawn indeed,

so you have a table looks like this

Red | Yellow | Green | Blue

and you want a combo box on form 2 to look like this based on that entry

Red
Yellow
Green
Blue

is that correct?

Make sure your Rowsource type is value list. column count is 1

Me.MyCombo.RowSource = DLookup("Field1","YourTable") & "; " & _
                       DLookup("Field2","YourTable") & "; " & _
                       DLookup("Field3","YourTable") & "; " & _
                       DLookup("Field4","YourTable")

J

[NeoPa]

.............................yawn................. ....................

Please do not express impatience at the service you're given.
Our experts and members offer their time voluntarily and do not deserve to be treated with impatience by those who hope to benefit from their efforts.

MODERATOR.

PS. A bump is perfectly acceptable after a wait of 24 hours, but your phrasing of it is not.