Take a field from selected record in list box...

[shenty]

I need to know how to pass a field from a selection in a list box to a field in another form (in Access 2003).

I have created a search box that uses a list box to display some records. There is a text box that a user enters details in to filter the list box down to less records. I want to be able to select one of these records in the list box and pass that field to another form.

I have uploaded the 2 forms in question. Basically i double click the Animal ID field in the main form and the Search Form loads. I start typing in the text box and the list box gets less and less to the point where i can select a record. What i want to do is populate the Selected Text text box with the text on the left (UK 162574 xxxxxx) and then click the save & exit button so it goes back to the main form and places the UK code into the Animal ID box.

Also as a minor point i can't seem to change the tick boxes from values to tick boxes in the list box.

If someone could help with some coding i'd be grateful - thanks all.

 

[shenty]

HELP with list box

http://www.access-programmers.co.uk/forums/showthread.php?t=130278

Can anyone help me with this thread ?
I want to know how to select something from my search listbox and use the contents of 1 column to place into the previous form as an entry

Thanks

 

[boblarson]

To pass a value from one form to the other, you have to first figure out when you want to do it. Do you want to do it when the second form closes or when you click on the list box? Or do you want a button to do it. So, for whichever event you choose you can use this syntax:

Forms!Form1NameHere.TextBoxNameWhereYouWantDataToGo = Me.YourListBoxNameHere

That will set the value for the bound column in the list box. If you want another column put into place instead, you can use

Forms!Form1NameHere.TextBoxNameWhereYouWantDataToGo = Me.YourListBoxNameHere.Column

Where n is a number of the column (zero based). So, if you wanted the info in the third column you would use:
Forms!Form1NameHere.TextBoxNameWhereYouWantDataToGo = Me.YourListBoxNameHere.Column(2)

 

[gemma-the-husky]

when you select the item in the listbox, the value of the listbox will be the selected item.

therefore in the afterupdate event you can then use the vlaue of "mylist" as you require

sub mylist_afterupdate

msgbox(mylist)
anotherformscontrols etc = mylist

etc


if you dont want to use the first column which is actually columns(0), you can use a different column with

mylist.columns(x)

 

[shenty]

Thanks BobLarson - that works a treat - it is what i was trying to do but my form had spaces in the name, i tried adding _ underscores in place of spaces but it didn't work so i ended up renaming all my tables to remove the spaces to prevent future problems.

I have another query now if anyone could help.

I have two tables that i want to combine into 1 query in date order - the first table contains information such as 'cattle id', 'date of birth', 'sex', 'breed' etc. ... the 2nd table contains Artificial Insemination dates & bull names (serving dates) for cattle. I want to combine the tables into one query that will list in 'ID' then date order.

Is this possible ?

Also in my list box there are 2 columns which are tick boxes but they show as -1 and 0 values, how can i change this?

Many thanks.