Vba adding record to table when field is integer

[Moss100]

Hello,

I have a form which has several textboxee which are set by user interaction.

When adding the record some of these text boxes will hold numeric keys for linking to other tables.

The fields in the table receiving these values is set to integer. I notice that is no value is held in the text box it causes the sql to fail.

How can I add a record, but add no value to number fields where appropriate. Thank you. Mark

 

[Andrzejek]

> add no value to number fields
Do those fields allow to have NULLs?

>it causes the sql to fail.
It would be nice to see your SQL...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Moss100]

Thank you as always - here is the code

 Dim RS As DAO.Recordset
  
 Set RS = CurrentDb.OpenRecordset("tbl_Archive", dbOpenDynaset)
       
  RS.AddNew
  
  RS("Arc_Date_Added") = Me.txt_Arc_Date_Added
  RS("Arc_Date_Updated") = Me.txt_Arc_Date_Added
  RS("Arc_SA_Purchaser_Link_ID") = Me.[txt_Arc_SA_Purchaser_Link_ID]
  RS("Arc_SA_Purchaser_Name") = Me.txt_Arc_SA_Purchaser_Name
  RS("Arc_System_Audit_Time") = Now()
     
  RS.Update
  RS.Close
           
  Set RS = Nothing

It fails on line "RS("Arc_SA_Purchaser_Link_ID") = Me.[txt_Arc_SA_Purchaser_Link_ID]" - the textbox [txt_Arc_SA_Purchaser_Link_ID] has a null or empty value.

Regards
Mark

 

[Andrzejek]

Do those fields allow NULLs?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Moss100]

Its just "RS("Arc_SA_Purchaser_Link_ID") = Me.[txt_Arc_SA_Purchaser_Link_ID]" that causes the problem.

The field is set as long integer - I dont think there is an option to allow or dis-allow nulls in a number field.

Thank you Mark

 

[Andrzejek]

>I dont think there is an option to allow or dis-allow nulls in a number field.
Why not..?

So, what is in the textbox [tt]txt_Arc_SA_Purchaser_Link_ID[/tt] when you have an error? Something the user types?

If you HAVE to have a value from User in this text box, why not:

If Me.txt_Arc_SA_Purchaser_Link_ID.Value & "" = "" Then[red]
  MsgBox "Hey!  Enter some value!"[/red]
Else
  Set RS = CurrentDb.OpenRecordset("tbl_Archive", dbOpenDynaset)  
  RS.AddNew
  RS("Arc_...
  ...
End If

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Moss100]

So the text box values are set from a combo box which has several columns.

So for example.

Txtbox1 = me.cbodata.column(0)
Txtbox2 = me.cbodata.column(1)
Etc………

If say column 2 contains no number then txtbox2 gets set as an empty string I think.

I think then the empty string (or perhaps null value) can not be inserted into the table as I think a number field can not be set as null or empty string.

The user has no control over the txtbox2 value as it’s set by the unseen values in the combo box.

I hope this explains ok. Thank you mark.

 

[Andrzejek]

So, working backwards, you need to have a value in Txtbox2, which gets its value from me.cbodata.column(1). I would assume your cbodata gets its data from some table in your database. (right?)
Check where this me.cbodata.column(1) data is coming from and why it is empty/NULL.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Moss100]

Hello Andy - so after tearing my hair out, i imported all objects into a new database and it works fine???????

Sorry to lead you on a wild goose chase - as always I much appreciate your help. Mark