listbox link to seperate form help

  • Thread starter Thread starter pclis007
  • Start date Start date
P

pclis007

Hello,

I have created a listbox ([lstInventory]) in form [frmInventory] that
displays all of my inventory. When I click on an inventory item in the
listbox, it takes me to a seperate form ([frmInventoryUpdate]) that displays
details regarding that specific inventory item.
My problem is: Although it takes me to the correct form, it isnt populating
with the details of the specific item selected from the listbox. The form
[frmInventoryUpdate] itself is linked to a query ([qryInventoryUpdate]) that
pulls all inventory data. Under the [Products] column of this query, I want
to add criteria that will populate [frmInventoryUpdate] with the selection I
made in the [frmInventory] listbox.
Any ideas on what to write in the criteria that will accomplish this?

Thanks,

Perry
 
Look in VBA Help for the OpenForm method. The example there shows
specifically how to do what you want to do. You need to 4th argument (Where
condition).

May I suggest you are going about this the hard way. It is not necessary to
have multiple forms. If you have just one form that allows users to work
with inventory items and use a Combo Box rather than a List Box to look up
the item, it will be easier on you and your users.
The advantage of a Combo is if you set the Auto Expand property of the combo
to Yes, the user can either use the drop down portion of the combo to find
the item or can use the text box portion to type in the item, and the combo
will show the firsst left to right match after each key stroke.
Once the user has located the item, you then use the After Update event of
the combo to make the record for the item the current record. You can also
give the user the opportunity to either add a new record for the value they
enter or cancel and start over.
Here is a sample that shows that technique:

Private Sub cboActivity_AfterUpdate()
Dim rst As Recordset
On Error GoTo cboActivity_AfterUpdate_Error

If Not IsNull(Me.cboActivity) Then
Set rst = Me.RecordsetClone
rst.FindFirst "[Activity] = '" & Me.cboActivity & "'"
If rst.NoMatch Then
If MsgBox("Add Activity Number " & Me.cboActivity & " To the
Attribute Table", _
vbYesNo + vbQuestion + vbDefaultButton2, "Activity Not
Found") _
= vbYes Then
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
Else
Me.cboActivity = Null
End If
Else
Me.Bookmark = rst.Bookmark
End If
Set rst = Nothing
Me.txtDescription.SetFocus
End If
cboActivity_AfterUpdate_Exit:

On Error Resume Next
rst.Close
Exit Sub

cboActivity_AfterUpdate_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure cboActivity_AfterUpdate of VBA Document
Form_frmAttributetable"
GoTo cboActivity_AfterUpdate_Exit

End Sub
 
Thanks for the code on that. I am doing something similiar with other parts
of my program, and it is definitely simpler.
However, for this particular person, he wants to be able to view all his
products in this listbox view, and be able to click on an individual product
to open up a form to update comments etc on that product. I'v seen it done,
I just can't figure out how.
Look in VBA Help for the OpenForm method. The example there shows
specifically how to do what you want to do. You need to 4th argument (Where
condition).

May I suggest you are going about this the hard way. It is not necessary to
have multiple forms. If you have just one form that allows users to work
with inventory items and use a Combo Box rather than a List Box to look up
the item, it will be easier on you and your users.
The advantage of a Combo is if you set the Auto Expand property of the combo
to Yes, the user can either use the drop down portion of the combo to find
the item or can use the text box portion to type in the item, and the combo
will show the firsst left to right match after each key stroke.
Once the user has located the item, you then use the After Update event of
the combo to make the record for the item the current record. You can also
give the user the opportunity to either add a new record for the value they
enter or cancel and start over.
Here is a sample that shows that technique:

Private Sub cboActivity_AfterUpdate()
Dim rst As Recordset
On Error GoTo cboActivity_AfterUpdate_Error

If Not IsNull(Me.cboActivity) Then
Set rst = Me.RecordsetClone
rst.FindFirst "[Activity] = '" & Me.cboActivity & "'"
If rst.NoMatch Then
If MsgBox("Add Activity Number " & Me.cboActivity & " To the
Attribute Table", _
vbYesNo + vbQuestion + vbDefaultButton2, "Activity Not
Found") _
= vbYes Then
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
Else
Me.cboActivity = Null
End If
Else
Me.Bookmark = rst.Bookmark
End If
Set rst = Nothing
Me.txtDescription.SetFocus
End If
cboActivity_AfterUpdate_Exit:

On Error Resume Next
rst.Close
Exit Sub

cboActivity_AfterUpdate_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure cboActivity_AfterUpdate of VBA Document
Form_frmAttributetable"
GoTo cboActivity_AfterUpdate_Exit

End Sub
Hello,
Click to expand...
[quoted text clipped - 13 lines]
Perry
Click to expand...
Click to expand...
 
He could still use the combo like a list box to look up the item; however, if
he insists on being narrow minded, as I posted earlier, you can use the Where
condition argument of the OpenForm method to open the other form on the
selected record. Use the current value of the list box for that. As it
shows in VBA Help example for the OpenForm method, you would have to identify
the item and the field in the recordset for the form.

pclis007 via AccessMonster.com said:
Thanks for the code on that. I am doing something similiar with other parts
of my program, and it is definitely simpler.
However, for this particular person, he wants to be able to view all his
products in this listbox view, and be able to click on an individual product
to open up a form to update comments etc on that product. I'v seen it done,
I just can't figure out how.
Look in VBA Help for the OpenForm method. The example there shows
specifically how to do what you want to do. You need to 4th argument (Where
condition).

May I suggest you are going about this the hard way. It is not necessary to
have multiple forms. If you have just one form that allows users to work
with inventory items and use a Combo Box rather than a List Box to look up
the item, it will be easier on you and your users.
The advantage of a Combo is if you set the Auto Expand property of the combo
to Yes, the user can either use the drop down portion of the combo to find
the item or can use the text box portion to type in the item, and the combo
will show the firsst left to right match after each key stroke.
Once the user has located the item, you then use the After Update event of
the combo to make the record for the item the current record. You can also
give the user the opportunity to either add a new record for the value they
enter or cancel and start over.
Here is a sample that shows that technique:

Private Sub cboActivity_AfterUpdate()
Dim rst As Recordset
On Error GoTo cboActivity_AfterUpdate_Error

If Not IsNull(Me.cboActivity) Then
Set rst = Me.RecordsetClone
rst.FindFirst "[Activity] = '" & Me.cboActivity & "'"
If rst.NoMatch Then
If MsgBox("Add Activity Number " & Me.cboActivity & " To the
Attribute Table", _
vbYesNo + vbQuestion + vbDefaultButton2, "Activity Not
Found") _
= vbYes Then
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
Else
Me.cboActivity = Null
End If
Else
Me.Bookmark = rst.Bookmark
End If
Set rst = Nothing
Me.txtDescription.SetFocus
End If
cboActivity_AfterUpdate_Exit:

On Error Resume Next
rst.Close
Exit Sub

cboActivity_AfterUpdate_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure cboActivity_AfterUpdate of VBA Document
Form_frmAttributetable"
GoTo cboActivity_AfterUpdate_Exit

End Sub
Hello,
Click to expand...
[quoted text clipped - 13 lines]
Perry
Click to expand...
Click to expand...
Click to expand...
 
Back
Top