query look up(?)

[Nick T]

Hi,
I use the following code in a click event on a command button in my form to
look up data in a table & insert the relevant info into text boxes on my
form. However, can I use this simular code to carry out the same function
but looking up the data in a query (as apposed to a table)??
I know it wont be exactly the same, but any suggestions on the code?
Any help would be great. Thanks.......

Private Sub Command9_Click()
Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("Addresses", dbOpenDynaset)
With rst
.FindFirst "[CustomerID] = " & Me.CustomerID


If .NoMatch Then
MsgBox "CustomerID " & Me.CustomerID & " Not Found in Client list"
Else
Me.FirstName = !FirstName
Me.LastName = !LastName
Me.PostalCode = !PostalCode

End If
Me.Command10.Enabled = True
.Close
End With
Set rst = Nothing

End Sub

 

[John Spencer]

How about doing something like the following. It should be faster since
you are not returning the entire table and are not then looking to see
if there is a match by scanning through the entire table.


Private Sub Command9_Click()
Dim rst As DAO.Recordset
Dim strSQL as String

strSQL = "SELECT FirstName, LastName, PostalCode" & _
" FROM Addresses WHERE CustomerID=" & Me.CustomerID


Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

If rst.RecordCount <1 then
MsgBox "CustomerID " & Me.CustomerID & _
" Not Found in Client list"
Else
Me.FirstName = RS!FirstName
Me.LastName = Rs!LastName
Me.PostalCode = Rs!PostalCode

End If

Me.Command10.Enabled = True
Set rst = Nothing

End Sub


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Maurice]

Yes you can, try replacing the tablename for the queryname. Also replace the
appropriate fieldnames to the fieldnames you use in your query.

 

[Nick T]

Hi,
Excelent, thanks for the help - worked great!

Another question i would appreciate help on - how can i change the amount of
decimal places on number data in a query. I just want eg. 10.50, but
whatever i seem to do to the query settings, i still have numbers such as
95.8923529411765. Is there anything i can put into my SQL or the query
field's criteria?? My SQL for the query in question is as follows if it
helps.....

SELECT DISTINCTROW [Customer Visit Log].[Customer ID], [Customer Visit
Log].[First Name], [Customer Visit Log].[Last Name], Format$([Customer Visit
Log].[Date of Visit],'yyyy') AS [Date of Visit By Year], Sum([Customer Visit
Log].[Spend Value (£)]) AS [Sum Of Spend Value (£)], Avg([Customer Visit
Log].[Spend Value (£)]) AS [Avg Of Spend Value (£)], Count(*) AS [Count Of
Customer Visit Log]
FROM [Customer Visit Log]
GROUP BY [Customer Visit Log].[Customer ID], [Customer Visit Log].[First
Name], [Customer Visit Log].[Last Name], Format$([Customer Visit Log].[Date
of Visit],'yyyy'), Year([Customer Visit Log].[Date of Visit]);

Yes you can, try replacing the tablename for the queryname. Also replace the
appropriate fieldnames to the fieldnames you use in your query.
--
Maurice Ausum

Hi,
I use the following code in a click event on a command button in my form to
look up data in a table & insert the relevant info into text boxes on my
form. However, can I use this simular code to carry out the same function
but looking up the data in a query (as apposed to a table)??
I know it wont be exactly the same, but any suggestions on the code?
Any help would be great. Thanks.......

Private Sub Command9_Click()
Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("Addresses", dbOpenDynaset)
With rst
.FindFirst "[CustomerID] = " & Me.CustomerID


If .NoMatch Then
MsgBox "CustomerID " & Me.CustomerID & " Not Found in Client list"
Else
Me.FirstName = !FirstName
Me.LastName = !LastName
Me.PostalCode = !PostalCode

End If
Me.Command10.Enabled = True
.Close
End With
Set rst = Nothing

End Sub

 

[Clif McIrvin]

Hi,
Excelent, thanks for the help - worked great!

Another question i would appreciate help on - how can i change the
amount of
decimal places on number data in a query. I just want eg. 10.50, but
whatever i seem to do to the query settings, i still have numbers such
as
95.8923529411765. Is there anything i can put into my SQL or the
query
field's criteria?? My SQL for the query in question is as follows if
it
helps.....

Yes, you can. I believe the standard advice here would be to use a form
instead of working directly with a query for presenting data to the
user; and in a form you have all the formatting options available to the
form's controls.

If you are now using a query to present and/or manipulate data and are
happy with the user interface use the Auto Form wizard to create a form
from your query and set the resulting form to datasheet view. From form
datasheet view you have the same appearance you see from the query,
*and* you have access to a wide range of formatting options, and the
ability to customize the behavior by adding event procedures, macros or
functions.

That being said (remember, forms are preferred for user interface for
many reasons) to set a format directly in a query open the query in
design view, right click in the field in question, select Properties ...
and set the format under the General tab of the Field Properties sheet
that opens up. (A2003)