Problems displaying the results of a query???

N

Nadine

Ok so I have a problem! I am creating a simple (or suppose to be
simple) Access application. On one of my pages users can select search
criteria from multiple drop downs to get their results. My problem is
displaying the results I have the qurey worked out and am now stuck!
Any help would be appreciated. Here is the code I have so far.

Private Sub Form_Load()

Dim strSQL As String

'Create a connection to the current Database
Dim cnHospList As ADODB.Connection
Set cnHospList = CurrentProject.Connection

strSQL = "Select * From [tblHospitalNames] "

If Forms!ViewHospBy![cmbProvider] <> " " Then
strSQL = strSQL & "Where [cihi_id] = " &
Forms!ViewHospBy![cmbProvider] & ""
End If

If Forms!ViewHospBy![cmbHAName] <> " " Then
strSQL = strSQL & " AND [ha_id] = " & Forms!ViewHospBy![cmbHAName]
& ""
End If

If Forms!ViewHospBy![cmbHSDAName] <> " " Then
strSQL = strSQL & " AND [hsda_id] = " &
Forms!ViewHospBy![cmbHSDAName] & ""
End If

If Forms!ViewHospBy![cmbLHAName] <> " " Then
strSQL = strSQL & " AND [lha_id] = " &
Forms!ViewHospBy![cmbLHAName] & ""
End If

strSQL = strSQL & " Order By [hosp_id]"

cnHospList.BeginTrans
cnHospList.Execute (strSQL)
cnHospList.CommitTrans

End Sub
 
J

Jeff Boyce

Nadine

I'm not sure about the SQL, but if you do that code in the Form_Load event,
it will happen before the user ever gets a chance to enter/select anything
on the form.

Another approach is to add a command button that creates the "on-the-fly"
SQL statement.

Good luck

Jeff Boyce
<Access MVP>
 
N

Nadine

This code is done on the form load of the page that I am using to
display the results not the page where they do there selecting. I am
still confused as to how to get the results of this qurey on the
page??? PLEASE anyone know?
 
J

Jeff Boyce

Nadine

A form (a screen, a "page") can be unbound, or can be bound to a recordset.
Both a table and a query return a recordset. Bind your "page" (in Access,
they're called "forms") to the query.

Good luck

Jeff Boyce
<Access MVP>
 
G

Gary Walter

Nadine said:
Ok so I have a problem! I am creating a simple (or suppose to be
simple) Access application. On one of my pages users can select search
criteria from multiple drop downs to get their results. My problem is
displaying the results I have the qurey worked out and am now stuck!
Any help would be appreciated. Here is the code I have so far.

Private Sub Form_Load()

Dim strSQL As String

'Create a connection to the current Database
Dim cnHospList As ADODB.Connection
Set cnHospList = CurrentProject.Connection

strSQL = "Select * From [tblHospitalNames] "

If Forms!ViewHospBy![cmbProvider] <> " " Then
strSQL = strSQL & "Where [cihi_id] = " &
Forms!ViewHospBy![cmbProvider] & ""
End If

If Forms!ViewHospBy![cmbHAName] <> " " Then
strSQL = strSQL & " AND [ha_id] = " & Forms!ViewHospBy![cmbHAName]
& ""
End If

If Forms!ViewHospBy![cmbHSDAName] <> " " Then
strSQL = strSQL & " AND [hsda_id] = " &
Forms!ViewHospBy![cmbHSDAName] & ""
End If

If Forms!ViewHospBy![cmbLHAName] <> " " Then
strSQL = strSQL & " AND [lha_id] = " &
Forms!ViewHospBy![cmbLHAName] & ""
End If

strSQL = strSQL & " Order By [hosp_id]"

cnHospList.BeginTrans
cnHospList.Execute (strSQL)
cnHospList.CommitTrans

End Sub
Click to expand...

Hi Nadine,

Pardon me for butting in, but it sure
looks like this is what you wanted?

Private Sub Form_Load()

Dim strSQL As String
Dim rs as ADODB.Recordset

Set rs = New ADODB.Recordset
rs.ActiveConnection = CurrentProject.Connection

strSQL = "Select * From [tblHospitalNames] "

If Forms!ViewHospBy![cmbProvider] <> " " Then
strSQL = strSQL & "Where [cihi_id] = " _
& Forms!ViewHospBy![cmbProvider]
Else
strSQL = strSQL & "WHERE (1=1) "
End If

If Forms!ViewHospBy![cmbHAName] <> " " Then
strSQL = strSQL & " AND [ha_id] = " & Forms!ViewHospBy![cmbHAName]
End If

If Forms!ViewHospBy![cmbHSDAName] <> " " Then
strSQL = strSQL & " AND [hsda_id] = " _
& Forms!ViewHospBy![cmbHSDAName]
End If

If Forms!ViewHospBy![cmbLHAName] <> " " Then
strSQL = strSQL & " AND [lha_id] = " _
& Forms!ViewHospBy![cmbLHAName]
End If

strSQL = strSQL & " Order By [hosp_id]"

rs.Open _
Source := strSQL, _
CursorType := adOpenKeyset, _
LockType := adLockOptimistic

Set Me.Recordset = rs

rs.Close
Set rs = Nothing

End Sub

Again, apologies for butting in
(especially if I was wrong).

Good luck,

Gary Walter
 
G

Gary Walter

Also....
a better form of test for your If/Then stmts might be

If Len(Trim(Forms!ViewHospBy![cmbProvider] & ""))>0 Then


Good luck,

Gary Walter


Gary Walter said:
Nadine said:
Ok so I have a problem! I am creating a simple (or suppose to be
simple) Access application. On one of my pages users can select search
criteria from multiple drop downs to get their results. My problem is
displaying the results I have the qurey worked out and am now stuck!
Any help would be appreciated. Here is the code I have so far.

Private Sub Form_Load()

Dim strSQL As String

'Create a connection to the current Database
Dim cnHospList As ADODB.Connection
Set cnHospList = CurrentProject.Connection

strSQL = "Select * From [tblHospitalNames] "

If Forms!ViewHospBy![cmbProvider] <> " " Then
strSQL = strSQL & "Where [cihi_id] = " &
Forms!ViewHospBy![cmbProvider] & ""
End If

If Forms!ViewHospBy![cmbHAName] <> " " Then
strSQL = strSQL & " AND [ha_id] = " & Forms!ViewHospBy![cmbHAName]
& ""
End If

If Forms!ViewHospBy![cmbHSDAName] <> " " Then
strSQL = strSQL & " AND [hsda_id] = " &
Forms!ViewHospBy![cmbHSDAName] & ""
End If

If Forms!ViewHospBy![cmbLHAName] <> " " Then
strSQL = strSQL & " AND [lha_id] = " &
Forms!ViewHospBy![cmbLHAName] & ""
End If

strSQL = strSQL & " Order By [hosp_id]"

cnHospList.BeginTrans
cnHospList.Execute (strSQL)
cnHospList.CommitTrans

End Sub
Click to expand...

Hi Nadine,

Pardon me for butting in, but it sure
looks like this is what you wanted?

Private Sub Form_Load()

Dim strSQL As String
Dim rs as ADODB.Recordset

Set rs = New ADODB.Recordset
rs.ActiveConnection = CurrentProject.Connection

strSQL = "Select * From [tblHospitalNames] "

If Forms!ViewHospBy![cmbProvider] <> " " Then
strSQL = strSQL & "Where [cihi_id] = " _
& Forms!ViewHospBy![cmbProvider]
Else
strSQL = strSQL & "WHERE (1=1) "
End If

If Forms!ViewHospBy![cmbHAName] <> " " Then
strSQL = strSQL & " AND [ha_id] = " & Forms!ViewHospBy![cmbHAName]
End If

If Forms!ViewHospBy![cmbHSDAName] <> " " Then
strSQL = strSQL & " AND [hsda_id] = " _
& Forms!ViewHospBy![cmbHSDAName]
End If

If Forms!ViewHospBy![cmbLHAName] <> " " Then
strSQL = strSQL & " AND [lha_id] = " _
& Forms!ViewHospBy![cmbLHAName]
End If

strSQL = strSQL & " Order By [hosp_id]"

rs.Open _
Source := strSQL, _
CursorType := adOpenKeyset, _
LockType := adLockOptimistic

Set Me.Recordset = rs

rs.Close
Set rs = Nothing

End Sub

Again, apologies for butting in
(especially if I was wrong).

Good luck,

Gary Walter
Click to expand...
 
N

Nadine

I think your right I think this is what I am looking for but when I
try and test it I am getting an error "No value given for one or more
required parameters". Then it high lights this peice of code:
rs.Open _
Source := strSQL, _
CursorType := adOpenKeyset, _
LockType := adLockOptimistic

When I mouse over the strSQL, adOpenKeyset, and adLockOptimistic I am
seeing values so what I am missing?

Gary Walter said:
Nadine said:
Ok so I have a problem! I am creating a simple (or suppose to be
simple) Access application. On one of my pages users can select search
criteria from multiple drop downs to get their results. My problem is
displaying the results I have the qurey worked out and am now stuck!
Any help would be appreciated. Here is the code I have so far.

Private Sub Form_Load()

Dim strSQL As String

'Create a connection to the current Database
Dim cnHospList As ADODB.Connection
Set cnHospList = CurrentProject.Connection

strSQL = "Select * From [tblHospitalNames] "

If Forms!ViewHospBy![cmbProvider] <> " " Then
strSQL = strSQL & "Where [cihi_id] = " &
Forms!ViewHospBy![cmbProvider] & ""
End If

If Forms!ViewHospBy![cmbHAName] <> " " Then
strSQL = strSQL & " AND [ha_id] = " & Forms!ViewHospBy![cmbHAName]
& ""
End If

If Forms!ViewHospBy![cmbHSDAName] <> " " Then
strSQL = strSQL & " AND [hsda_id] = " &
Forms!ViewHospBy![cmbHSDAName] & ""
End If

If Forms!ViewHospBy![cmbLHAName] <> " " Then
strSQL = strSQL & " AND [lha_id] = " &
Forms!ViewHospBy![cmbLHAName] & ""
End If

strSQL = strSQL & " Order By [hosp_id]"

cnHospList.BeginTrans
cnHospList.Execute (strSQL)
cnHospList.CommitTrans

End Sub
Click to expand...

Hi Nadine,

Pardon me for butting in, but it sure
looks like this is what you wanted?

Private Sub Form_Load()

Dim strSQL As String
Dim rs as ADODB.Recordset

Set rs = New ADODB.Recordset
rs.ActiveConnection = CurrentProject.Connection

strSQL = "Select * From [tblHospitalNames] "

If Forms!ViewHospBy![cmbProvider] <> " " Then
strSQL = strSQL & "Where [cihi_id] = " _
& Forms!ViewHospBy![cmbProvider]
Else
strSQL = strSQL & "WHERE (1=1) "
End If

If Forms!ViewHospBy![cmbHAName] <> " " Then
strSQL = strSQL & " AND [ha_id] = " & Forms!ViewHospBy![cmbHAName]
End If

If Forms!ViewHospBy![cmbHSDAName] <> " " Then
strSQL = strSQL & " AND [hsda_id] = " _
& Forms!ViewHospBy![cmbHSDAName]
End If

If Forms!ViewHospBy![cmbLHAName] <> " " Then
strSQL = strSQL & " AND [lha_id] = " _
& Forms!ViewHospBy![cmbLHAName]
End If

strSQL = strSQL & " Order By [hosp_id]"

rs.Open _
Source := strSQL, _
CursorType := adOpenKeyset, _
LockType := adLockOptimistic

Set Me.Recordset = rs

rs.Close
Set rs = Nothing

End Sub

Again, apologies for butting in
(especially if I was wrong).

Good luck,

Gary Walter
Click to expand...
 
N

Nadine

Ok so I figured out why I was getting the "No value given for one or
more required parameters" and the code you gave me works in returning
the results I want but how do I get the results to display??? Right
now I have text boxes on the form unbound and when I run the formload
it is pulling back 87 blank text boxes? So how do I get the records to
appear? I tried doing the control source but because the record source
is coming from code so I can't just use the control source drop down
feature. I did some searching thinking that I could assing the control
source of the text box in code and read that I can not do this. Is
that true? Please help I need to get these records to display on my
form?

Gary Walter said:
Nadine said:
Ok so I have a problem! I am creating a simple (or suppose to be
simple) Access application. On one of my pages users can select search
criteria from multiple drop downs to get their results. My problem is
displaying the results I have the qurey worked out and am now stuck!
Any help would be appreciated. Here is the code I have so far.

Private Sub Form_Load()

Dim strSQL As String

'Create a connection to the current Database
Dim cnHospList As ADODB.Connection
Set cnHospList = CurrentProject.Connection

strSQL = "Select * From [tblHospitalNames] "

If Forms!ViewHospBy![cmbProvider] <> " " Then
strSQL = strSQL & "Where [cihi_id] = " &
Forms!ViewHospBy![cmbProvider] & ""
End If

If Forms!ViewHospBy![cmbHAName] <> " " Then
strSQL = strSQL & " AND [ha_id] = " & Forms!ViewHospBy![cmbHAName]
& ""
End If

If Forms!ViewHospBy![cmbHSDAName] <> " " Then
strSQL = strSQL & " AND [hsda_id] = " &
Forms!ViewHospBy![cmbHSDAName] & ""
End If

If Forms!ViewHospBy![cmbLHAName] <> " " Then
strSQL = strSQL & " AND [lha_id] = " &
Forms!ViewHospBy![cmbLHAName] & ""
End If

strSQL = strSQL & " Order By [hosp_id]"

cnHospList.BeginTrans
cnHospList.Execute (strSQL)
cnHospList.CommitTrans

End Sub
Click to expand...

Hi Nadine,

Pardon me for butting in, but it sure
looks like this is what you wanted?

Private Sub Form_Load()

Dim strSQL As String
Dim rs as ADODB.Recordset

Set rs = New ADODB.Recordset
rs.ActiveConnection = CurrentProject.Connection

strSQL = "Select * From [tblHospitalNames] "

If Forms!ViewHospBy![cmbProvider] <> " " Then
strSQL = strSQL & "Where [cihi_id] = " _
& Forms!ViewHospBy![cmbProvider]
Else
strSQL = strSQL & "WHERE (1=1) "
End If

If Forms!ViewHospBy![cmbHAName] <> " " Then
strSQL = strSQL & " AND [ha_id] = " & Forms!ViewHospBy![cmbHAName]
End If

If Forms!ViewHospBy![cmbHSDAName] <> " " Then
strSQL = strSQL & " AND [hsda_id] = " _
& Forms!ViewHospBy![cmbHSDAName]
End If

If Forms!ViewHospBy![cmbLHAName] <> " " Then
strSQL = strSQL & " AND [lha_id] = " _
& Forms!ViewHospBy![cmbLHAName]
End If

strSQL = strSQL & " Order By [hosp_id]"

rs.Open _
Source := strSQL, _
CursorType := adOpenKeyset, _
LockType := adLockOptimistic

Set Me.Recordset = rs

rs.Close
Set rs = Nothing

End Sub

Again, apologies for butting in
(especially if I was wrong).

Good luck,

Gary Walter
Click to expand...
 
G

Gary Walter

Nadine said:
Ok so I figured out why I was getting the "No value given for one or
more required parameters" and the code you gave me works in returning
the results I want but how do I get the results to display??? Right
now I have text boxes on the form unbound and when I run the formload
it is pulling back 87 blank text boxes? So how do I get the records to
appear? I tried doing the control source but because the record source
is coming from code so I can't just use the control source drop down
feature. I did some searching thinking that I could assing the control
source of the text box in code and read that I can not do this. Is
that true? Please help I need to get these records to display on my
form?
Click to expand...

Hi Nadine,

Looks to me like you have 2 options.

1) Just design a new form using tblHospitalNames.

Once you are satisfied with the design,
unbind it from tblHospitalNames,
then use the code in the form_open event
of this new form.

Delete your old form and rename this new form
to old form name.

2) Comment out the form_open code,

bind your form to tblHospitalNames,

complete the design,

then unbind from tblHospitalNames,
and restore your form_open code.

Please respond back if I have misunderstood
or was not clear about something.

Good luck,

Gary Walter
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Capturing a Value in a Text box before update 1
Listbox and combo problem 7
Multiple results from query 6
Form closing and it's not suppose to be! 1
How to show results of a VBA Query. 2
Problems with a query; result to textbox. 2
Crosstab query does not display all results 1
Using a Form to Create a Query. Problem with VBA code... 2

Top