Referencing a value against another table

[Jeremiah31]

I need some help on this request if anybody has time. What I’m trying to accomplish is to have a popup message display if a model number has been pre-identified as salvage.

I have a table called tblMainTable bound to a form called “frmMainDataEntry”.

“tblMainTable” has data entry fields

txtModelNumber
txtSKU
txtPallet

“tblWarning”
strModelNumber
YesNoWarning

On the frmMainDataEntry the control is called “txtModelNumber”

My thought process was to create a second table and bound it a form where the supervisor can enter the model Number and use a yes/no field to mark if the model number is salvage or not. Using code, if the value is found in the tblWarning and marked as true the message box would appear, if not, no activity would occur.

This is the code I’m come up with so far… Having problem using including a yes/no value into the with statement.


Private Sub txtModelNumber_LostFocus
Dim db As DAO.Database
Dim rs As Recordset ' the recordset object
Dim BolSalavage As Boolean
Dim strModelNumber As String
'
' create local variables
'
Dim strRSName As String
'
' initialize the variables
' Contains predefined Model Number by user with yes/no box.
' Checked box (true) means salvage, unchecked means not salvage.
'
strRSName = "tblWarning"
'
' setting the database and recordset properties
'
Set db = CurrentDb
Set rs = db.OpenRecordset(strRSName, dbOpenDynaset)
'
' Using With statement
'
With rs
' "strModelNumber" belongs to the tblWarning Field, me.txtModelNumber is a control on the form
.FindFirst "[strModelNumber]='" & "' Trim(me.txtModelNumber)" & ""
If BolSalavage = True Then
MsgBox ("Model Number is marked as ""Salvage""")
Exit Sub
End If
End With

End Sub

 

[CaptainD]

Looks like you are using MS Access, not VB. If so, someone here might send you to another forum.

As far as your fix, since it's a boolean entry tied to the vehicles record, that's bound to the form. Why not just add a field to the main table and bind a check box for that field on the same form?

To do a search, you can set up a form filter to bring up the record for the vehicle or scroll a listbox.