Find and Display Contents of Field in 1st Record

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've got a switchboard form that opens upon opening of my database. I want
to display the ImportDate contained in tblCombinedData on this switchboard
form.

I'm looking for the VBA code that would open tblCombinedData, move to the
first record, and display the contents of a field called ImportData in a text
box called txtImportData. I believe this would run much faster than my
SELECT DISTINCT SQL query, which works, but runs too slowly over the network.

Thanks!
 
Kirk said:
I've got a switchboard form that opens upon opening of my database. I want
to display the ImportDate contained in tblCombinedData on this switchboard
form.

I'm looking for the VBA code that would open tblCombinedData, move to the
first record, and display the contents of a field called ImportData in a text
box called txtImportData. I believe this would run much faster than my
SELECT DISTINCT SQL query, which works, but runs too slowly over the network.
Click to expand...

You only want to get the value from the first record in the
table??

If the table has more than one record, there is no such
thing as the "first" record. Tables are an unordered bag of
data. OTOH, if you have a field that defines "first" (e.g.
a date field), then you could use a query such as:

SELECT TOP 1 ImportDate
FROM tblCombinedData
ORDER BY datefield

If the table only has one record, then a simple DLookup
should be sufficient:

DLookup("ImportDate", "tblCombinedData")
 
Your SELECT Top 1 suggestion works - it's much faster than the SELECT
DISTINCT I originally had.

But, just to better understand.....

My table (tblCombinedData) contains 93,000 records, and the ImportDate is
exactly the same for all these records. The Select Distinct gave me the
correct answer, but was slow to execute over the network. I thought another
solution would be to open the recordset called tblCombinedData, move to the
first record using the MoveFirst method, and display the contents of the
field called ImportDate in my text box. I'm just not able to get the syntax
quite right for such a procedure, but wouldn't that idea also be a viable
solution?
 
ALL the records have the same value in that field? Sounds a
little strange to me, but I guess I don't need to go into
that. Whatever!

Your idea would be viable and the syntax would be something
like:
Me.textbox = rs!ImportDate
A MoveFirst is not needed. First, because that's where the
recordset is positioned when it's opened and second because
you don't care what record you get the value from.

Actually, since it doesn't matter what record the value
comes from, you can just use a text box expression:
=DLookup("ImportDate", "tblCombinedData")
or
=DFirst("ImportDate", "tblCombinedData")
both of which will get the value from an arbitrary record,
not necessarily what you might think is the "first".
 
Back
Top