Hi all, I have this issue.
On Form A i have an unbounded textbox [Search]and a command button. I want
to click on the command button and open form B were the data in
[FormA][Search] should correspond to [FormB][BagsID].[Table Bags] If no
records were found I would like to display a simple message “no records
found”…
Command button “on click” event”
=
Form A. Unbounded textbox [Search]
Form B .Bounded textbox [BagsID] Table [Bags]
Is this a dlook up and If Is not or..i dont understand the sequense of the
event..?
Thanks!
I had the similar circumstance where I wanted to look a report based
off of three criteria.
I am assuming this is similar to what you want. This code builds an
SQL and looks for the recordset matching the criteria in the "search"
form (or FormA as you call it) and if the recordset doesn't exist, it
ask the user if they would like to add it.
Should be able to modify it some to get what you want, attach to the
ON CLICK event on your "SEARCH" button.
This maybe a little overkill.
*****CODE START*****
Private Sub cmdFindAdd_Click()
'requires a reference set for Microsoft DAO 3.x Object Library
On Error GoTo HandleError
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strForm As String
Dim strDialog As String
Dim Criteria1 As String
Dim intAnswer As Integer
Dim Response As Integer
'all three criteria required
'If any criteria is missing, say so
If Nz(Me.formCriteria_date, "") = "" Or Nz(Me.formCriteria1, "") =
"" Then
MsgBox "Missing Date, Shift, or Area! Please enter all
criteria before clicking find.", vbOKOnly, "Oops!"
Exit Sub
Else
Set db = CurrentDb()
'create SQL
strSQL = "SELECT BagsID FROM Bags"
strSQL = strSQL & " WHERE [formCriteria_Date] = #" & [Forms]!
[FORMA]![formCriteria_date] & "# "
strSQL = strSQL & " AND [formCriteria1] = '" & [Forms]![FORMA]!
[formCriteria1] & "' ;"
' open recordset
Set rs = db.OpenRecordset(strSQL)
'check for records
If rs.BOF And rs.EOF Then
'Send msgbox to ask wether to enter data or if entry was a
mistake
intAnswer = MsgBox("There is no current entry for " &
vbCrLf & Chr(34) & Me.formCriteria_date & _
" " & Me.formCriteria1 & _
" Shift " & _
" " & Chr(34) & "." & vbCrLf & _
"Would you like to add it to now?"
_
, vbQuestion + vbYesNo, "No Report
Found?")
Select Case intAnswer
Case vbYes
'set cursor to hourglass
DoCmd.Hourglass (True)
'open form in add mode
DoCmd.OpenForm "FORMB", acNormal, , ,
acFormAdd, acWindowNormal
'set lookups for criteria to input to form
Forms!FORMB.formCriteria_Date.Value =
Me.formCriteria_date
Forms!FORMB.formCriteria1.Value =
Me.formCriteria1
DoCmd.Hourglass (False)
strDialog = "FORMA"
DoCmd.Close acForm, strDialog, acSaveNo
Exit Sub
Case vbNo
'Tell user to try again, resume
MsgBox "Please try again." _
, vbInformation, "Data Entry"
Exit Sub
End Select
Else
'set cursor to hourglass
DoCmd.Hourglass (True)
rs.MoveLast
rs.MoveFirst
'---uncomment to check record
selection----------------------------------------------------
'MsgBox "Recordset ID: " & rs!
[BagsID], vbOKOnly, "SQL RECORD SELECT"
'------------------------------------------------------------------------------------------
'--- Uncomment to see How many records for the criteria
specified---
'If rs.RecordCount = 1 Then
'MsgBox rs.RecordCount & " record found"
'Else
'MsgBox rs.RecordCount & " records found"
'End If
'------------------------
DoCmd.OpenForm "FORMB", acNormal, , "[BagsID] = " & rs!
BagsID
DoCmd.Hourglass (False)
End If
End If
HandleError_Exit:
On Error Resume Next
Exit_cmdFindAdd_Click:
'clean up
rs.Close
Set rs = Nothing
Set db = Nothing
strDialog = "FORMA"
DoCmd.Close acForm, strDialog, acSaveNo
Exit Sub
HandleError:
MsgBox Err.Number & " - " & Err.Description
Resume HandleError_Exit
End Sub
*****CODE END*****
