Continuing with my project with your help the code takes the user input on CGBox # and looks to see if it's already in process or not, if not it goes ahead and adds it. Problem now is when it finds a CGBox # already in the database it adds a new record with only the CGBox # only and no other data, so it's like a record with one field entered and the rest is blank. So what I need it to do is when a CGBox # is entered that is already in Process to just give the message "Box is already In-Process" and move on to the next input, without entering the CGBox # that was already there. I tried to add the following code:
Code:
[Forms]![frm_CGBoxStatusIn]![txtCGBox] = "" If Len(Me.txtCGBox) = "" Then
rs.AddNew = False
which didn't work.....so I'm stuck....I will add the whole set of code so you can see what it's doing.
Code:
Public Sub cmd_Update_Scan_In_Click()'On Error GoTo Err_Command5_Click
'Stop
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim str_CGBox As String
Dim str_SQL As Variant
Dim stDocName As String
Dim stLinkCriteria As String
Dim str_ScanIn
Dim Message As String
Dim Title As String
Dim MyValue
Dim Default
Dim Answer As Integer
Dim MyNote As String
Dim int_MsgBox As Integer
Dim str_Msg As String
Set db = CurrentDb
str_CGBox = [Forms]![frm_CGBoxStatusIn]![CGBox]
'Stop
str_SQL = "SELECT CGBoxStatusIn.CGBox " & _
"FROM CGBoxStatusIn " & _
"WHERE (((CGBoxStatusIn.CGBox)='" & [Forms]![frm_CGBoxStatusIn]![txtCGBox] & "'));"
Set rs = db.OpenRecordset(str_SQL, dbOpenDynaset, dbSeeChanges)
'check to see if any records in record set.
If rs.BOF And rs.EOF Then 'if rs.BOF = true And rs.EOF = true, then no records in record set
'MsgBox "Box is not in database"
Else
MsgBox "Box is already In-Process"
[Forms]![frm_CGBoxStatusIn]![txtCGBox] = ""
If Len(Me.txtCGBox) = "" Then
rs.AddNew = False
'Call cleanupblankScans
stDocName = "frm_CGBoxStatusIn"
stLinkCriteria = "(((CGBoxStatusIn.CGBox)='" & str_CGBox & "'))"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Call Form_Load
Exit Sub
End If
'Clean up
rs.Close
Set rs = Nothing
Set db = Nothing
'Text for Yes/No Message Box
MyNote = "Is the Box Full?"
'Display Message Box
Answer = MsgBox(MyNote, vbYesNo, "???")
If Answer = vbYes Then
'Code for Yes button Press
str_CGBox = [Forms]![frm_CGBoxStatusIn]![CGBox]
[Forms]![frm_CGBoxStatusIn]![ScanIn] = Now()
Else
Message = "How many are missing?"
Title = "CG Box"
Default = ""
MyValue = InputBox(Message, Title, Default)
[Forms]![frm_CGBoxStatusIn]![missing] = MyValue
[Forms]![frm_CGBoxStatusIn]![ScanIn] = Now()
End If
DoCmd.Close
stDocName = "frm_CGBoxStatusIn"
stLinkCriteria = "(((CGBoxStatusIn.CGBox)='" & str_CGBox & "'))"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_cmd_Done_Click:
Exit Sub
Err_Command5_Click:
MsgBox Err.Description
Resume Exit_cmd_Done_Click
End Sub
Private Sub Command8_Click()
DoCmd.Close acForm, "frm_CGBoxStatusIn"
DoCmd.OpenForm "frm_Welcome"
End Sub
Private Sub Form_Load()
'On Error GoTo Err_Form_Load
DoCmd.GoToRecord , , acNewRec
Me!txtCGBox = ""
Me!txtCGBox.SetFocus
Exit_Form_Load:
Exit Sub
Err_Form_Load:
MsgBox Err.Description
Resume Exit_Form_Load
End Sub
Thanks in advance,
Lenny