Populate FK field with code?

[pablavo]

Hi everyone.

I'm having a problem with my code. Hope someone can help.

This concerns two tables. tblCountry and tblProjectMain. tblCountry is the parent table.

I'm using a combo box to select a country which in turn I can allocate a Project to.
when the country is selected I'll press the Cmd and it will add the project to tblProjectMain.

If the Country already has a project within the tblProjectMain I can use the Cmd to allocate another project so for example
Country: Uganda. Project: Uga 002 (002 being the second project within that country.)
If it's a new country and there hasn't been a record created for the tblProjecMain yet and I try and execute the Cmd I get "94, invalid use of Null"
I believe this is because the code doesnt add the FK to tblProjectMain.

Does anyone know how I add this within the code so I can execute the Cmd?

Thankyou for any help in advance.

This is the code if it helps.


Dim strCountry As String, strNum As String, strSuffix As String, strFullCode As String
Dim strWhere As String

strCountry = Me.cboPrefixCountry
strWhere = "[PrefixCountry]='" & strCountry & "'"
strNum = DMax("ProjectCode", "TblProjectMain", strWhere)
If strNum <> "" Then
strNum = strNum + 1
Else
strNum = 1
End If
strNum = Format(strNum, "000")
strSuffix = 1
strFullCode = strCountry & " - " & strNum & "/" & strSuffix


Me.txtNewProjectCode = strFullCode
Me.txtNewProjectCode.Visible = True
Me.cmdAccept.Visible = True

(the Projectcode is the Project number for that country (001) etc.)
(I've ommited the Error handling for space!)

 

[ajetrumpet]

If it's a new country and there hasn't been a record created for the tblProjecMain yet and I try and execute the Cmd I get "94, invalid use of Null"

It's probably because the DMAX function cannot return a value if the lookup request (value) is not present in the table. I may be wrong, but it would seem to me that if the DMAX value is not in the table, it would return an error, because your ELSE statement performs a task IF the DMAX returned value IS blank. If there is no record in the table for the country, it is not blank. It just isn't there.

    strNum = DMax("ProjectCode", "TblProjectMain", strWhere)
    [COLOR="Red"][U]If strNum <> "" Then[/U][/COLOR]
        strNum = strNum + 1
    Else
        strNum = 1

I wonder if, instead of that, maybe this would work...??

strNum = DMax("ProjectCode", "TblProjectMain", strWhere)
    [COLOR="Red"][U]If Not IsNull(strNum) Then[/U][/COLOR]
        strNum = strNum + 1
    Else
        strNum = 1

If it doesn't, maybe you could use DCOUNT to check and see if there is a record in the table that contains the combo box value (country name/code). That would certainly work here...

 

[pablavo]

Thanks for your help AjeTrumpet.

In the end I used something similar which was

strNum = Nz(DMax("ProjectCode", "TblProjectMain", strWhere), 0)

And this worked.

Thanks again.