Listbox to a form

[Dorian C. Chalom]

OK now I have my list box fully populated.
Now I want to be able to select a record from the ListBox and open a form up
based on that record.
I have it where I doubleclick on the row and it opens the form.
But my question is how do I bring over the selected record?

Thanks for your help.

 

[Dirk Goldgar]

in message

OK now I have my list box fully populated.
Now I want to be able to select a record from the ListBox and open a form
up based on that record.
I have it where I doubleclick on the row and it opens the form.
But my question is how do I bring over the selected record?

Presumably you have either a macro or a VBA event procedure triggered by the
list box's DblClick event. If it's a macro, it probably executes the
OpenForm action; if a VBA event procedure, it probably calls the
DoCmd.OpenForm method. Either way, it passes an argument that tells what
form to open. The OpenForm action or method also accepts a WhereCondition
argument, and you can use it to specify a criterion for the record to be
displayed by the form.

I'll assume for the sake of argument that you have a VBA event procedure,
and that it includes a line of code something like this:

DoCmd.OpenForm "YourFormName"

You need to change that to specify a criterion that will select the specific
record that was double-clicked in the list box. The code might look
something like this:

DoCmd.OpenForm "YourFormName", _
WhereCondition:="[YourIDField]=" & Me.lstYourListbox

You should replace "YourFormName" with the name of the form to be opened,
"YourIDField" with the name of the primary key field of the table on which
that form is based, and "lstYourListbox" with the name of the list box.

Note: the above assumes that [YourIDField] is a numeric field. If it's a
text field, you need to wrap quotes around the value you pick up from the
list box, so that it looks something like this:

DoCmd.OpenForm "YourFormName", _
WhereCondition:="[YourIDField]='" & Me.lstYourListbox & "'"

The above assumes that the single-quote character (') won't appear in the
key field; if it does, other modifications must be made.

 

[Dorian C. Chalom]

Thank you Dirk.

Now lets assume based on the record I choose I am creating a new record on
the form based on the record selected from the list box.

How do I do that? Can that be done?

Thank you for your help.

in message

OK now I have my list box fully populated.
Now I want to be able to select a record from the ListBox and open a form
up based on that record.
I have it where I doubleclick on the row and it opens the form.
But my question is how do I bring over the selected record?

Presumably you have either a macro or a VBA event procedure triggered by
the list box's DblClick event. If it's a macro, it probably executes the
OpenForm action; if a VBA event procedure, it probably calls the
DoCmd.OpenForm method. Either way, it passes an argument that tells what
form to open. The OpenForm action or method also accepts a WhereCondition
argument, and you can use it to specify a criterion for the record to be
displayed by the form.

I'll assume for the sake of argument that you have a VBA event procedure,
and that it includes a line of code something like this:

DoCmd.OpenForm "YourFormName"

You need to change that to specify a criterion that will select the
specific record that was double-clicked in the list box. The code might
look something like this:

DoCmd.OpenForm "YourFormName", _
WhereCondition:="[YourIDField]=" & Me.lstYourListbox

You should replace "YourFormName" with the name of the form to be opened,
"YourIDField" with the name of the primary key field of the table on which
that form is based, and "lstYourListbox" with the name of the list box.

Note: the above assumes that [YourIDField] is a numeric field. If it's a
text field, you need to wrap quotes around the value you pick up from the
list box, so that it looks something like this:

DoCmd.OpenForm "YourFormName", _
WhereCondition:="[YourIDField]='" & Me.lstYourListbox & "'"

The above assumes that the single-quote character (') won't appear in the
key field; if it does, other modifications must be made.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

in message

Thank you Dirk.

Now lets assume based on the record I choose I am creating a new record on
the form based on the record selected from the list box.

How do I do that? Can that be done?

Can it be done? Of course? How? That depends. I seem to have done too
much assuming, up to now.

What is the RowSource of the list box? Does it have multiple columns
including the field value(s) you want to appear in the new record you are
creating?

What is the RecordSource of the form you are opening? What fields do you
want to fill in from the record selected in the list box?

 

[Dorian C. Chalom]

The RowSource is a Query
Yes multiple columns (9) some of the fields if not all need to appear in
the new record.
The recieving form is based off a Table.

Thank you...

 

[Dirk Goldgar]

The RowSource is a Query

And what is the SQL of that query?

Yes multiple columns (9) some of the fields if not all need to appear in
the new record.

What columns? And what fields in the new record are they supposed to go
into?

The recieving form is based off a Table.

And the fields in that table are ...? Are the controls on the form named
the same as the fields in the table?

Do you want to create a new record with these values, or change an existing
one?

The more detailed information you give me, the less time I'll waste guessing
and setting up vague examples.