How to get first record of a table

[pdauction]

Hi,

I'm thinking this is obvious so apologies if it is (I have spent quite some time searching for the answer but can't seem to find it).

I have a parameters table that has in it one field called Month End date there is only one record which contains the month end date (user enters this via a form).

From this I want to fire off a macro to import text tables from the relevant folders (that have month and year references).

I'm OK with the import macro except I just need to set to variables mmm and yyyy depending on what the first (and only) record says in my table called Parameters.

Not sure what I need...

mmm = Month(Tables!Parameters.Recordset.......?

I thought about referencing the actualltextbox on the form and retrieving the value there but this didn't seem the best way as the user could accidently be on record 2 (blank).

Many Thanks

 

[jdraw]

Your idea of record number/position may be causing you difficulty.
The actual storage of data in relational tables is controlled by the database software and system routines. Any record may be positioned anywhere, so you retrieve records based on select queries.

Select "my desired fields" FROM MyTable
WHERE field1 = "deisredValue1" and field2 = "desiredValue2" etc.

This is not dependent on position of the record, but the values that distinguish that record from all others in the Table.

 

[pdauction]

jdraw,

Thanks fior getting back, that did seem to be my sticking point. I have now managed to solve this (I think);

    Dim mmm As String, yyyy As String
    Dim rstGetMonthEndDate As Recordset
    Dim mthenddte As Date
        
'Get Month End Date
Set rstGetMonthEndDate = CurrentDb.OpenRecordset("Parameters")
rstGetMonthEndDate.MoveFirst
 
mthenddte = rstGetMonthEndDate![Month End Date]
  
rstGetMonthEndDate.Close
Set rstGetMonthEndDate = Nothing

 

[jdraw]

A couple of points.

Put a debug.priint mthendate statement right before
rstGetMonthEndDate.Close
and you will get the value of mthendDate in the immediate window. This is a common debugging technique.

I would strongly advise you to NOT use a naming convention that allows spaces and special characters in field and object names. These will come back to "bite" you.

I don't think you have solved the issue just by a Move first against your recordset.

If it works, I think it would be by chance. If you add more records to the Parameters table, you could not guarantee the record in the first position.

Select Yourfieldname From Parameters
WHERE (condition to get the unique record you need)

will work every time.

 

[pdauction]

Cheers for the debugging technique, I'll remember that as I normally use a clumsy Msgbox.

Also thanks for the tip regarding spaces - I have come across problems before with this and was tempted to rename my field, again something I need to remember for the future.

My code does seem to work as there should only ever be one record in the table, however I may fall foul if the user accidently adds a new record via the form.

Using the where statement seems difficult (to me!) as the unique record would change each month when the database would be used so not sure what my condition would be.

 

[gemma-the-husky]

if you only have 1 record, just use a dlookup.

dlookup without criteria, returns a random record - but if there is only one record, thats the one you get.

just

controlvalue = dlookup("somefield","controltable")