Not entering Record

[PatAccess]

Hello All,
I have the following VBA Code, however, every field gets entered other than the LicName into Tbl_EngineerLic. Can someone help me please?

Thank you,

Private Sub Cmd_EnterNewCA_Click()
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("SELECT ID FROM Tbl_StateLic WHERE [StateID]='" & Me.State.Value & "' AND [LicID]='" & Me.Lic.Value & "'")
Dim StateLic As Integer
Dim ls As Recordset
Set ls = CurrentDb.OpenRecordset("SELECT ID FROM Tbl_LicName WHERE [LicNameID]='" & Me.LicName.Value & "'")
rs.MoveFirst
StateLic = rs("ID")
Dim qryStr As String
qryStr = "INSERT INTO Tbl_EngineerLic (EmpID,StateLic,LicNameID,LicNum,DateEarned,Expires,Comments)"
Dim valStr As String
valStr = "VALUES ('" & Me.EmpID.Value & "'," & StateLic & "," & Me.LicName.Value & ",'" & Me.LicNum.Value & "',#" & Me.DateEarned.Value & "#,#" & Me.ExpDate.Value & "#,'" & Me.Comments.Value & "')"
qryStr = qryStr & "" & valStr
DoCmd.RunSQL qryStr
End Sub

 

[pbaldy]

If it's a text field, the value needs to be surrounded with apostrophes. This should help:

http://www.baldyweb.com/ImmediateWindow.htm

 

[Orthodox Dave]

Possibly the reverse is happening. I see you have included apostrophes around the value of an ID field. If this is numeric, the apostrophes may cause an error. So if StateID and LicID are numeric then

this code:

WHERE [StateID]='" & Me.State.Value & "' AND [LicID]='" & Me.Lic.Value & "'")

should be changed to this:

WHERE [StateID]=" & Me.State.Value & " AND [LicID]=" & Me.Lic.Value)

There are others, but you get the drift.

To answer your original question, the reason you aren't populating LicName is an apparent typo in your query:

qryStr = "INSERT INTO Tbl_EngineerLic (EmpID,StateLic,[I][B]LicNameID[/B][/I],LicNum,DateEarned,Expire s,Comments)"

Your valStr shows the above LicNameID being populated by Me.LicName.Value

Just as a comment I would suggest tidying up the code a bit. You declare and set the recordset "ls" but never seem to use it. And it is customary to make all the declarations (Dim) at the start. It's just to make it easier to read.

 

[PatAccess]

It was something wrong with the " and '. I got it to work somewhat but it is giving me issues when some of the fields are left blank. Why?
It wants every fields to be filled out. What if I want to leave comments blank?
Thank you for the help

 

[Orthodox Dave]

Declare a variable for Me.Comments.Value and give it an empty string value if null as:

Dim Com as string
If IsNull(Me.Comments.Value) then
Com = ""
Else
Com = Me.Comments.Value
End If

Then in your Insert Values, you substitute Com for Me.Comments.Value

(Access hates nulls!)

 

[pbaldy]

Alternatively, build the INSERT INTO and VALUES clauses separately, and only add fields to each that have values.

 

[PatAccess]

Thank you Guys! The If IsNull statement worked.

Thanks a Bunch. Don't miss me too much, I have more coming