Using last value in a new record field

[Pgill]

Rpeare, thanks for the comments.... I have learnt something new here "Ctrl ' " handy little shortcut, thanks for that..... Why I wanted the entry from the last record in the new record is, you may only use 2 or 3 selections, but use say one selection the most.... it will automatically be placed there..... but if you do decide to use one of the other selections, you just type it in and then the next record will start with the new selection..... It is more of a time management solution than anything else..... but is something useful.
Paul, what I have done at the moment till I can sort out something that works is to use a drop down listbox..... it is not the answer, but will help in the meantime. I will carry on looking into this problem through doing some code learning, and then I can place the answer here to teach others that may want to know.....
Thanks a lot for the help, I will use another test base to play with this problem and try things like adding a table and Dlookup...... etc....

God bless
Peter

[rpeare]

let's say you have this data set:

RecordID PersonID ItemID
1       1        1
2       1        1     
3       1        1   
4       1        2

Let's assume for the sake of this question that this is the only data you have. When you go to the data entry form for record 5 (the new record) do you want the ITEMID to be filled in with 2 (the most recent ITEMID) OR do you want it to be 1 (the most used ITEMID) your last post made it muddy to me.

In either case it can be handled on your data entry form. In the ON ENTER property of your ITEMID field you could have this code:

Dim db As Database
Dim rst As Recordset
Dim ssql As String

If ItemID = 0 Or IsNull(ItemID) Then
    Set db = CurrentDb
    ssql = "SELECT * FROM tbl_Data2 WHERE (([PERSONID]) = " & Me.PersonID & " AND ([RECORDID]) < " & Me.RecordID & ") ORDER BY RECORDID DESC"
    Set rst = db.OpenRecordset(ssql)
    
    rst.MoveFirst
    ItemID = rst.Fields("itemid")
    rst.Close
    Set db = Nothing
Else
    Exit Sub
End If

Which would find the most recent record by recordID and use the value in the ITEMID field to populate the current record. You can determine what gets populated by adjusting the SQL statement so that the record you want always appears first.

[Pgill]

Hi Rpeare,

Thanks for the reply :-) sorry about being vague.... let me try and make it clear....

I have designed a Treasure hunting Database...... One of the fields is called: Detector Type. In this field as in my case, I have two metal detectors a Ace 250, and a Tesoro.... The one I use the most is the Ace 250......
Now I have two options:
1) Create a drop down list with both detectors in it... I then use the drop down and choose the correct detector.......
2) Or I create a txtfield, and type in record No 1, 2, Tesoro in Record No 3, Ace250....

What I want is when I add the next new record No 4..... The last entry the Ace 250 should populate the field automatically on entry to new record.... If I do want the Tesoro, I would type it in manually,,,, then the next new record No 5 will automatically have Tesoro entered into it......

Hope that helps?

Thanks again for any help...

[rpeare]

The code I posted looks at the most recent record (by primary key ID, assuming it's an autonumber) and returns the most recent value from that list other than the current record which is what you're telling me you want in your most recent post, you just have to adapt the code to your table, form and field names.

[Pgill]

Hi Rpeare,

Thanks for all your help.... I have copied the code to Notepad and will create a testbase and give it a try.... This Forum is great and very helpful..... I will let you know what happens....