Display result from VBA into a control of form

  • Thread starter Thread starter Karen Huynh
  • Start date Start date
K

Karen Huynh

Hi
This question was answered by a few people a few months back but
unfortunately its been deleted of the server...so i was hoping that someone
could help me answer it again.

I would like the form to run a specific query when specific options are
selected.
Here i what my code looks like so far:

Private Sub cmdsearch_Click()
Dim db As Database
Dim rst As Recordset
Dim sql As String

Set db = CurrentDb

Me.txtEdate.SetFocus
strEdate = Me.txtEdate.Text
Me.txtSdate.SetFocus
strSdate = Me.txtSdate.Text

If strSdate = "" And strEdate = "" Then
MsgBox "Please enter in the dates", , "Required Information
Missing"
ElseIf strSdate > strEate Then
MsgBox "Please make sure the date is right.", , "Required
information missing"
Else
GoTo searching
End If
searching:

'start looking at specific options
If frmopt.Value = 1 And Me.FrRmtype.Value = 1 Then
sql = "SELECT * FROM roomlist" & _
"WHERE roomtype = 'Single'"

end sub

I'm not sure what to do next. I would like a form to open up with a list
showing all the information matching the sql criteria in a datasheet view.

If anyone, can help, that would be greatly appreciated

Thanks
 
I couldn't see which form you are opening but I assume that you have all
the fields in that NewForm as those in your SQL statement in the end of
your code. Try this then:

Private Sub cmdsearch_Click()
Dim Rst As ADODB.Recordset
Dim Sql As String

strEdate = Me.txtEdate.Text
strSdate = Me.txtSdate.Text

If strSdate = vbNullString And strEdate = vbNullString Then
MsgBox "Please enter in the dates", , "Required Information Missing"
goto, ExitHere
Else
If strSdate > strEdate Then
MsgBox "Please make sure the date is right.", , "Required information missing"
goto, ExitHere
End If
End IF

'start looking at specific options
If frmopt.Value = 1 And Me.FrRmtype.Value = 1 Then Sql = "SELECT * FROM
roomlist" & _
"WHERE roomtype = 'Single'"
Set Rst as New ADODB.Recordset
Rst.Open Sql, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
if Rst.RecordCount = 0 then Goto, ExitHere
DoCmd.OpenForm "NewForm"
Forms.Items("NewForm").Recordset = Rst
Rst.Close
Set Rst = Nothing
ExitHere:
Exit Sub
end sub

I didn't test it - air code.
Cheers,
Pavel
 
Cant help you directly with your query, but you should know that you can
search Google archives for all news messages for at least the last several
years. Go to Google, and ask for advanced group search, something like:

http://groups.google.com/groups?hl=en&lr=&q=&btnG=Google+Search&meta=group%3
Dmicrosoft.public.access.formscoding

Its a *huge* resource - invaluable !

--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control
 
Back
Top