Dlookup…DoCmd.OpenForm..or?

[Peter]

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!

 

[Stefan Hoffmann]

hi Peter,

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â€â€¦

Private Sub cmdSearch_Click()

Dim Filter As String

Filter = "([Table Bags] = '" & txtSearch.Value & "')"
DoCmd.OpenForm "yourFormB", , Filter

End Sub

btw, [FormB][BagsID].[Table Bags] makes not really sense.


mfG
--> stefan <--

 

[BruceM via AccessMonster.com]

You could look at the record count when FormB opens, and generate a message
if the count is zero. However, you could also use a combo box that gets its
Row Source from the Bags field (or whatever field is represented by the value
in the unbound text box in your example) in the FormB RecordSource. For
example, if the Record Source for FormB is TableB, make a query from the Bags
field in TableB, and use that query as the Row Source for the combo box on
FormA. In that way you will be presented only with values that actually
exist in FormB's Record Source.

An alternative to the Filter argument of OpenForm is the Where condition.

Private Sub cmdSearch_Click()

Dim strWhere as String

strWhere = Me.ComboBoxName

DoCmd.OpenForm "FormB", , , strWhere

End Sub

As I understand, the Filter argument allows all records to be loaded, but
restricts what you see to the records that meet the Filter condition. The
Where argument, on the other hand, limits the recordset that is loaded just
to the records that meet the Where condition. If you want to see all records
you can remove a Filter, but you need to redefine the recordset if you use
the Where condition. The choice depends on your needs.

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!

 

[DeDBlanK]

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*****