Select List box data and populate form

[mousemat]

I have a form with several list boxes on all working fine.

One of the list boxes shows the current quotations for the current customer.

I would like to be able select a line in the list box and then click a command button to open the Invoice Form and have the fields populated by the data from the list box.

The underlying query for the listbox has all the fields that are required for the Invoice form but I can't seem to work out how to transfer the data from the list box to the second form.

Any ideas?

 

[Mile-O]

• If you don't close the form with the listbox then you can reference all the information;
• You can pass all the values via the OpenArgs and then use a function to split the components down;
• You can use DLookups to get all the values as you say it's based on a query.

 

[mousemat]

Thanks for your response Mile

The underlying query for the listbox has 12 fields however I have only chosen to have 3 fields in the listbox simply for a more pleasing effect on the eye, I don't want the horizontal scroll bars!

I have tried referencing the lsitbox box data but only getting the 3 fields that are visible in the lookup box. Do i really need to show all 12 fields inthe lookup box to acheive this?

 

[Mile-O]

Depends how you want to do it; I gave you three possible solutions.

 

[mousemat]

I know, Sorry

I like the idea of the openargs but dont know how to implement it really.

 

[Mile-O]

The open args is passed when opening a form - it's the last argument in the DoCmd.OpenForm "MyForm" method.

Basically, you can do a little loop that collects all the values in the listbox's row and separate them with a delimiter of your own choice. You can get a bit of code I posted from the Code Library called GetPart which helps to extract the components you want.

You can have all the fields in the listbox and just set the ColumnWidths property to 0 for each column you don't want to show. Remember to set the ColumnCount to reflect the number of fields you have.

 

[mousemat]

Thanks for that Mile

I'll have a bash at it now.

 

[jon2192]

i used a macro with the sendto command.
it can send a value from one form to another.

If you are using a list box. you need to add all the fields in the query, put the ones you want to view first. and set column count to the number you want to view. so column 3 would be accessed as form.list.(2)
As the column count starts at zero.

If you need more help just ask. As this is a bit vague and i havent got my db here to check exactly how i did it.