New record from listbox selection

stonward

Hi,

I'm (now) a moderately experienced Access developer, but I'm currently stuck with a problem i can't seem to get over.

I have a filtered listbox of products on a form and I want to use the selection (single select) of the listbox to start a new record in a subform below it.

Can anyone help? I would be muchly grateful.

Thanx folks,

Roy, WSM.

Rabbit

Quote:

Originally Posted by stonward

Hi,

I'm (now) a moderately experienced Access developer, but I'm currently stuck with a problem i can't seem to get over.

I have a filtered listbox of products on a form and I want to use the selection (single select) of the listbox to start a new record in a subform below it.

Can anyone help? I would be muchly grateful.

Thanx folks,

Roy, WSM.

Well, you could use the after update event or the on click event of the listbox. That would then fill in the fields of the subform. You want to consider what happens if they make accidental clicks and such.

stonward

Thanks, Rabbit,

...working on your proposal now - will let you know!

PS...can you tell me what 'PM' ing is?!

stonward

I was using the dbl_Click event anyway, along with a whereCondition and addNew statement.
Tried the on_click event with a macro (goToRecord(new)) but keep getting a message that the object (subform) is closed (oh no it isn't). Even putting a SelectObject in front doesn't cure this. I'm going to continue to battle this to the end, but any further help would be great for my ulcers!

Thanks again,

Stonward Roy.

Rabbit

Quote:

Originally Posted by stonward

I was using the dbl_Click event anyway, along with a whereCondition and addNew statement.
Tried the on_click event with a macro (goToRecord(new)) but keep getting a message that the object (subform) is closed (oh no it isn't). Even putting a SelectObject in front doesn't cure this. I'm going to continue to battle this to the end, but any further help would be great for my ulcers!

Thanks again,

Stonward Roy.

Here's the thing about subforms, they're not considered their own object. So you can't use the go to new record on the subform if you're not in the form.

PM is private messaging.

You could try the go to new record on Forms![Main Form]![Subform Name].Form but I don't think that will work.

stonward

Gonna keep trying - tho' another way may be easier, eh?!

Thanx for all your help, folks.

Roy.

damonreid

This might be of some help. The below code inserts a value into another table on command.

Expand|Select|Wrap|Line Numbers

    Dim MyRST As Recordset

    Set MyRST = CurrentDb.OpenRecordset("TABLE NAME", dbOpenDynaset)

                With MyRST

                    .AddNew

                    ![FIELD NAME] = Me.CONTROL.Value

                    .Update

    End With

You can then set the command double click to open a new form with this record? I know it isn't what you wanted but might be a step in the right direction.

stonward

Cool. Thanks for that - I reckon that could be the answer.

Thanks again to all who helped!

Stonward Roy

New record from listbox selection

re: New record from listbox selection

re: New record from listbox selection

re: New record from listbox selection

re: New record from listbox selection

re: New record from listbox selection

re: New record from listbox selection

re: New record from listbox selection

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

/bytes/about