record order 1

[robdunfey]

I use the following code to add a new record to a database. It works fine sometimes. Sometimes after requerying the table it understands the first record entered to be the last. Has anybody had similar problems? Does anybody know how th docmd.gotorecord,,acfirst works?

Any help, much appreciated.

Rob


' Insert the next row to the line table

DoCmd.SetWarnings False
DoCmd.RunSQL ("insert into ENG_LINE ( [SITE_ID],[LINE_CNT_ID]) values ('" & Me![SITE_ID] & "'," & NoLines + 1 & &quot&quot
DoCmd.SetWarnings True

' Requry the table

Me.Requery

' Move to the last record

DoCmd.GoToRecord , , acLast

' Set the line variables

NoLines = NoLines + 1
CurrentLine = NoLines

' Update the button status

Call UpdateButtons

 

[danvlas]

Looks like you are using a form (Me keyword)
What is the recordsource of your form (especially the Order By clause)?
If no Order By, or if the form is based directly on the table, then I suspect the records are ordered by the primary key or not ordered at all. Whenever you requery the recordsource, it is re-created from scratch.
To make sure the record you entered last will fall at the bottom of the recordset, base your form on a query like:

Select * from Eng_Line Order By Site_ID;

That is assuming Site_ID is numeric and always incrementing.

HTH,

Dan

 

[robdunfey]

Dan,

Thanks. I have a hunch that this might have something to do with it. You are correct it is a form based on a table. I have tried to explain the problem better but I have two question that I hope you could help me with. In the form 'Properties' the record source is the table. The table has two primary keys, site id and 'line' number (eg. see below, letters represent site id and numbers represent lines). Any site can have several lines.

How do I set the form to a query as you suggested?
What is the filter property of the form?

Any further help, much appreciated.

Rob

A1
B1
C1
C2
C3
C4
D1
E1
E2
F1
G1

So when Site ID = C and I click add line button I want to insert a new record after C4. It does all this but when I requery the form sometimes it displays C1 (the first entry) as the last C entry instead of C5 (the last entry).

 

[danvlas]

Select * from Eng_Line Order By Site_ID, LINE_CNT_ID;

will sort first the Site_ID (alphabetically), then each 'group' will be sorted by Line_Cnt_Id.

Just as a clarification: you can't have more than one primary key in a table. You probably have a primary key based on two fields.

HTH,

Dan

 

[robdunfey]

Dan,

It certainly does appear to have helped thanks. I am still very much a novice with the database stuff. You say you canot have two primary keys. Indeed all the stuff I have read says only one primary key. But when I check the table it has two keys? At least two key symbols?

But anyhow, thanks for your help.

Rob

 

[danvlas]

Yes, two key symbols mean you have a multi-field primary key. That is: you can enter duplicate values in either field, but the combined value will always be unique.
Example:

FirstName LastName
John Smith
John Wayne
Bill Smith
John Smith


When you have a single field primary key (only one key symbol), you won't be able to enter duplicate values in that field. Green values indicate duplicate values in one field. Red line: it would be a duplicate combination.

Hope this is more clear now

Dan