Populate new record's foreign key field automatically

[sarah_anne]

I have a form where all records are listed on one page with a vertical scrollbar (all simple textboxes):

----------------------------------------------
PrimaryKey Name ForeignKey
----------------------------------------------
1 Pam 20
2 John 20
3 Kim 20
4 Larry 20

... underneath these on the form is a blank record for NEW additions, which obviously has nothing in any of its textboxes, waiting for user to input some data, as normal.

The Foreign Key, due to the nature of my DB, will always be the SAME value for each record in the DB (don't ask, it's part of an export/import-to-main-db app). So I know that the NEW record, if created, will have the same value as the rest of the records.

How do I get it so that when a new record is created, the Foriegn Key textbox is automatically filled in with the value from the row above? (Due to referential integ reasons the Foreign Key field MUST be filled in accuratly or else an error ocurrs.)

Looking at the various events, I realise that it is the BeforeInsert event that will sort this out, but I'm at a loss regarding how to get the NEW record's Foreign Key textbox to match the Foreign Key value above it.

Any advice?

 

[sarah_anne]

Doesn't matter, it's as simple as what I've shown below (thought I'd best post up the solution here in case anyone needs this for the future!)...

In the BeforeInsert event:

DoCmd.GoToRecord , , acPrevious ' going to previous record
Dim vid As String ' declaring a variable to store my foreign key id in
var = ForeignlID.Value ' assigning the previous one's foreign key to var
DoCmd.GoToRecord , , acNext ' go to next record (the new record)
ForeignID.Value = var ' put the value of var (an ID) into its foreign key field!

Hope that helps someone else when/if they come looking!

 

[quest4]

You could also just have entered that value in the control's DefaultValue. Just my two cents her.

 

[sarah_anne]

Just discovered that my method does NOT work if there are no previous records in the table, should have been obvious but oh well.

I've tried using the DefaultValue setting in the textbox's controls, and the Expression Builder pops up. I specify the table/field I'd like to get the default foreign key value from, and its code is "= [Computers]![ComputerID]" i.e. in the "Computers" table, field "ComputerID[pk]".

This doesn't work?

edit: Sorry, to clarify rather than just "it doesn't work", i meant in the textfield "#Name" appears rather than my Primary Key in the "Computers" table.

I realise I'm not specifying a RECORD anywhere for the Computers table, to get the value from ComputerID field... help...?

 

[quest4]

The value you are looking for is in the table, right? If so, try using DLookUp in the control's GotFocus event. As long as you are not working in a large dbase, DLookUp should work OK for you. Another idea would be to use a recordset and search via a loop. Outside of that, I am out of ideas. hth.