Change Row Source to Query!

[Bob Vance]

Row Source to my List Box!
Can I change my row source to a query instead of direct to the table?
And is there any disadvantage in doing this?

SELECT tblInvoice_ItMdt.IntermediateID, tblInvoice_ItMdt.HorseID,
funGetHorse(0,tblInvoice_ItMdt.HorseID,false) AS Name,
tblInvoice_ItMdt.TotalAmount FROM tblInvoice_ItMdt INNER JOIN tblHorseInfo
ON tblInvoice_ItMdt.HorseID=tblHorseInfo.HorseID ORDER BY
funGetHorse(0,tblInvoice_ItMdt.HorseID,true),
funGetHorse(0,tblInvoice_ItMdt.HorseID,false);

 

[Bob Quintal]

Row Source to my List Box!
Can I change my row source to a query instead of direct to the
table? And is there any disadvantage in doing this?

SELECT tblInvoice_ItMdt.IntermediateID, tblInvoice_ItMdt.HorseID,
funGetHorse(0,tblInvoice_ItMdt.HorseID,false) AS Name,
tblInvoice_ItMdt.TotalAmount FROM tblInvoice_ItMdt INNER JOIN
tblHorseInfo ON tblInvoice_ItMdt.HorseID=tblHorseInfo.HorseID
ORDER BY funGetHorse(0,tblInvoice_ItMdt.HorseID,true),
funGetHorse(0,tblInvoice_ItMdt.HorseID,false);

Yes you can. There are no disadvantages, and often advantages.
You can even enter (type or copy/paste) the SQL directly into the
rowsource of a listbox (or combobox, so that you don't have a saved
querydef in the queries list.

 

[John Spencer]

Yes, you can use a query and not a table.

There is no penalty for doing so and you get the benefit of being able
to display the records in a sorted order.

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

 

[Hans Up]

Row Source to my List Box!
Can I change my row source to a query instead of direct to the table?
Yes.

And is there any disadvantage in doing this?

SELECT tblInvoice_ItMdt.IntermediateID, tblInvoice_ItMdt.HorseID,
funGetHorse(0,tblInvoice_ItMdt.HorseID,false) AS Name,
tblInvoice_ItMdt.TotalAmount FROM tblInvoice_ItMdt INNER JOIN tblHorseInfo
ON tblInvoice_ItMdt.HorseID=tblHorseInfo.HorseID ORDER BY
funGetHorse(0,tblInvoice_ItMdt.HorseID,true),
funGetHorse(0,tblInvoice_ItMdt.HorseID,false);

Disadvantage to what? Using a query as a list box RowSource or using
*that* particular query?

If you want to use the same query in other areas of your application,
you might find it more convenient to save the query and give it a name.
Then you can reference that query by name wherever you need it in your
application.

As far as the query you included above, I have 3 suggestions to offer.

1. Name is a reserved word. I would change it to HorseName. See:
http://allenbrowne.com/AppIssueBadWord.html#N

2. Consider using short aliases for your table names.

3. You're joining on tblHorseID, but not referencing any fields from
that table. Maybe you need tblHorseID in there for some reason; if not,
eliminate it.

Here's how I would rewrite it.

SELECT
inv.IntermediateID
, inv.HorseID
, funGetHorse(0,inv.HorseID,false) AS HorseName
, inv.TotalAmount
FROM
tblInvoice_ItMdt AS inv
INNER JOIN tblHorseInfo AS hi
ON inv.HorseID=hi.HorseID
ORDER BY
funGetHorse(0,inv.HorseID,true),
funGetHorse(0,inv.HorseID,false);

I just find that style a whole lot easier to comprehend when I have to
figure out why a query isn't doing what I want.

Good luck,
Hans

 

[John W. Vinson]

Row Source to my List Box!
Can I change my row source to a query instead of direct to the table?

Simply view the properties of the listbox and change its RowSource property to
your choice of the name of the stored Query (assuming it's a stored query,
which might be used elsewhere in the database) or to the SQL string.

And is there any disadvantage in doing this?

I don't think I've used a combo or listbox based on a Table in years. Queries
let you sort, limit the fields and records included, etc.

SELECT tblInvoice_ItMdt.IntermediateID, tblInvoice_ItMdt.HorseID,
funGetHorse(0,tblInvoice_ItMdt.HorseID,false) AS Name,
tblInvoice_ItMdt.TotalAmount FROM tblInvoice_ItMdt INNER JOIN tblHorseInfo
ON tblInvoice_ItMdt.HorseID=tblHorseInfo.HorseID ORDER BY
funGetHorse(0,tblInvoice_ItMdt.HorseID,true),
funGetHorse(0,tblInvoice_ItMdt.HorseID,false);

The only downside I see is that this will be slowed down considerably by the
repeated calls to the funGetHorse function. Any chance that you could replace
the function by using tblHorseInfo, which is already joined to
tblInvoice_ItMdt? What does funGetHorse do?

 

[Bob Vance]

Simply view the properties of the listbox and change its RowSource
property to
your choice of the name of the stored Query (assuming it's a stored query,
which might be used elsewhere in the database) or to the SQL string.


I don't think I've used a combo or listbox based on a Table in years.
Queries
let you sort, limit the fields and records included, etc.


The only downside I see is that this will be slowed down considerably by
the
repeated calls to the funGetHorse function. Any chance that you could
replace
the function by using tblHorseInfo, which is already joined to
tblInvoice_ItMdt? What does funGetHorse do?

Thanks John' it basically gives the horses name if unnamed as
Sire-Dam-Age-Sex till such time as it gets a name!

Function funGetHorse(Optional lngInvoiceID As Long = 0, Optional lngHorseID
As Long = 0, Optional bHorse As Boolean = False) As Variant

Dim recHorseID As New ADODB.Recordset, strAge As String, strName As String
Dim recHorseName As New ADODB.Recordset

If lngHorseID = 0 And lngInvoiceID = 0 Then
funGetHorse = ""
Exit Function
End If

If lngHorseID = 0 Then
recHorseID.Open "SELECT HorseID FROM tblInvoice WHERE InvoiceID=" _
& lngInvoiceID, CurrentProject.Connection, adOpenDynamic,
adLockOptimistic

If recHorseID.EOF = True And recHorseID.BOF = True Then
Set recHorseID = Nothing
funGetHorse = ""
Exit Function
End If
lngHorseID = recHorseID.Fields("HorseID")
End If

recHorseName.Open "SELECT * FROM tblHorseInfo WHERE HorseID=" _
& lngHorseID, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

If recHorseName.EOF = True And recHorseName.BOF = True Then
Set recHorseName = Nothing
funGetHorse = ""
Exit Function
End If

If IsNull(recHorseName.Fields("HorseName")) Or
recHorseName.Fields("HorseName") = "" Then

'If flg is true and Horse Name is null then Horse Name is set as
blank.
If bHorse = False Then
If IsNull(recHorseName.Fields("DateOfBirth")) Or
recHorseName.Fields("DateOfBirth") = "" Then
strAge = "0yo"
Else
strAge = funCalcAge(Format(CDate("01-Aug-" &
recHorseName.Fields("DateOfBirth")), "dd-mmm-yyyy"), Format(Now(),
"dd-mmm-yyyy"), 1)
End If
strName = Nz(recHorseName.Fields("FatherName"), "") & " -- " &
Nz(recHorseName.Fields("MotherName"), "") _
& " " & strAge & " " & Nz(recHorseName.Fields("Sex"), "")

Else
strName = ""
End If
Else
strName = recHorseName.Fields("HorseName")
End If

Set recHorseID = Nothing
Set recHorseName = Nothing
funGetHorse = strName
End Function

 

[John W. Vinson]

Thanks John' it basically gives the horses name if unnamed as
Sire-Dam-Age-Sex till such time as it gets a name!

Since those fields aren't all in the table, you probably don't have much
choice!

 

[Bob Vance]

Well actually they are all in tblHorseInfo but if [name] is blank then it
uses 4 other fields to give the horse a alternative name..Regards Bob

 

[Bob Vance]

Oops sorry FatherName, MotherName, Age, Sex are the 4 fields
Bob

 

[John W. Vinson]

Oops sorry FatherName, MotherName, Age, Sex are the 4 fields
Bob

If these are all fields in the table then you don't need all the recordset
manipulation:

NZ([name], "By " &[FatherName] & " out of " & [MotherName] & ", " & [Age] & "
" & [Sex])

But don't use Name as the Name of a field, since fields already have a Name
and Access will get just as confused as this sentence; and certainly don't use
the same fieldname as both the stored horsename field and the calculated name.

 

[Bob Vance]

Just realized I need the FunGetHorse as It calculates the Horses age
changing each year on August the 1st
Regards Bob

Oops sorry FatherName, MotherName, Age, Sex are the 4 fields
Bob

If these are all fields in the table then you don't need all the recordset
manipulation:

NZ([name], "By " &[FatherName] & " out of " & [MotherName] & ", " & [Age]
& "
" & [Sex])

But don't use Name as the Name of a field, since fields already have a
Name
and Access will get just as confused as this sentence; and certainly don't
use
the same fieldname as both the stored horsename field and the calculated
name.