Value on another table

  • Thread starter Thread starter Rpettis31
  • Start date Start date
R

Rpettis31

Brain dead today ~I have a list box with values (Items list) and I am not
sure how I go about looking up if the selected value is on a table (item
details). I know I can listbox.column(1).value to get the item from the list
box but how do I look for it on another table (item details).

Basically if the item selected is in the details show the detail record if
not create a new record. I understand the docmd.openform add or edit record.
I am just not sure how to create the condition "if the item selected is not
on the item details table then open form create new record" else open the
item detail record."
 
Rpettis31 said:
Brain dead today ~I have a list box with values (Items list) and I am not
sure how I go about looking up if the selected value is on a table (item
details). I know I can listbox.column(1).value to get the item from the list
box but how do I look for it on another table (item details).

Basically if the item selected is in the details show the detail record if
not create a new record. I understand the docmd.openform add or edit record.
I am just not sure how to create the condition "if the item selected is not
on the item details table then open form create new record" else open the
item detail record."
Click to expand...


Try something like"

If DCounr("*", "item details", "[the field] = " _
& listbox.column(1)) > 0 Then
' open form in edit mode
Else
' open form in add mode
End If
 
I suppose one way might be to use the DCount() (?or DLookup()) function to
see how many records are in the table with the listbox's value. You'd
probably need to "wrap" the Nz() function around it to handle when there are
none.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Rpettis31 said:
Brain dead today ~I have a list box with values (Items list) and I am not
sure how I go about looking up if the selected value is on a table (item
details). I know I can listbox.column(1).value to get the item from the
list
box but how do I look for it on another table (item details).

Basically if the item selected is in the details show the detail record if
not create a new record. I understand the docmd.openform add or edit
record.
I am just not sure how to create the condition "if the item selected is
not
on the item details table then open form create new record" else open the
item detail record."
Click to expand...


What field in what table? In principle, the code would look something like
this:

'----- start of example "air code" -----
Dim strValue As String
Dim blnExists As Boolean

strValue = Me.lstMyListbox.Column(1) & vbNullString

If Len(strValue) = 0 Then
' Nothing was selected, or it has no value.
' Handle this as you want.
Else
' Set blnExists to indicate whether this value is
' found in the table.
blnExists = _
Not IsNull(DLookup("SomeField", "[Item Details]", _
"SomeField='" & strValue & "'"
End If

If blnExists Then
DoCmd.OpenForm "frmItemDetails", _
WhereCondition:="SomeField='" & strValue & "'"
Else
' Open form to new record.
DoCmd.OpenForm "frmItemDetails", DataMode:=acFormAdd
' Initialize field in new record to the selected value.
Forms!frmItemDetails!SomeField = strValue
End If
'----- end of code -----

Note that the above code assumes that this is a text value, and that it will
never contain the single-quote character. It must be modified if either of
those assumptions is incorrect.
 
Back
Top