Updating FK with VBA?

[pablavo]

Hi there

The database I’m working on is Specific to storing info on Projects around the world.

I’m using code that will create a new project record. Each project is identified with a country abbreviation (UGA which is Uganda) and a project number (002). The code will use the existing country abrrev and add to the existing number. In this case the code will create “UGA 003” which will occupy two fields within the main table. Then the code will open a form so that the rest of the fields can be filled in.

Here’s the problem. The Main table is joined to a lookup table that stores all the countries and Country abrrev’s. (Uganda – UGA) so combo boxes can be created (within forms using the country field as the row source) to allow users to select which ever country project to view (in a report for example)

The join is created using the CountryID which is the PK wihin tblCountry lookup table.
Because the Key for the country lookup table is the FK on the Main table, it’s not updated and just shows “0” within the main table

I’m trying to look for a way to update the FK (CountryID field) on the Main table with code. So if the PK for UGA within the Country Lookup table is 13, this will show on the Main table’s FK when a new project code is created.

Does anyone know how to do this? Any help will be great.

Here’s the code if anyone’s interested.

Private Sub cmdAddProjectCode_Click()

Dim VCountry, vNum, vSuffix, vFullCode
VCountry = Me.cmdCountryAbrrev
vNum = DMax("NumberCode", "tblProjectMain", "CountryAbbrev= forms!frmNewProjectCode!cmdCountryAbbrev") '';
If vNum <> "" Then
vNum = vNum + 1
Else
vNum = 1
End If
vNum = Format(vNum, "000")
vSuffix = 1
vFullCode = VCountry & " - " & vNum & "/" & vSuffix
vFullCode = InputBox("Please enter new Project code: CCC - SSS, or accept suggested code.", "NumberCode", vFullCode)
If vFullCode <> "" Then
DoCmd.OpenForm "frmNewProjectDetails", , , , acFormAdd, , vFullCode

End If
SendKeys "{F9}"
End Sub

 

[DJkarl]

Need more information, what data type is the FK?

 

[pablavo]

Thanks for replying DJKarl!

The FK is Numeric as it's just a copy of the AutoNumber from tblCountrys' PK.

While executing the mentioned code, I'm trying add code that will update the tblProjectMains CountryID field (the FK)

Let me know if you want more info

Cheers