Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 445
  • Last Modified:

How to transfer a null value on a form to a table

I have produced a database which will allow people to add customer records, edit the data on a form, then trasfer the data into the table, but if the text boxes on the form are left blank , it doesn’t work! How can I get it to work if the value in the text boxes are null??? Please help
This is a sample of my code:

Private Sub btaddtotable_Click()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim varTextDataA As String
Dim vartextdataB As String
Dim vartextdataC As String
Dim vartextdataD As String
Dim vartextdataE As String
Dim vartextdataF As String

varTextDataA = srn
vartextdataB = invoice2
vartextdataC = BuyerName2
vartextdataD = BuyerAdd1
vartextdataE = BuyerAdd2
vartextdataF = BuyerAdd3

Set db = CurrentDb
Set rst = db.OpenRecordset("UK Sales", dbOpenDynaset)

    rst.AddNew
        rst!SalesRecordNumber = varTextDataA
        rst!InvoiceDate = vartextdataB
        rst!BuyerFullName = vartextdataC
        rst!BuyerAddress1 = vartextdataD
        rst!BuyerAddress2 = vartextdataE
        rst!BuyerAddress3 = vartextdataF


0
ridgejason
Asked:
ridgejason
  • 3
1 Solution
 
mbizupCommented:
For anything that allows Nulls, declare them as Variant not String.
0
 
jawa29Commented:
Hi

You need to allow null entries on the database columns affected.

In SQL this is done in Design View there's a tick at the end of each column that says Allow Nulls.

Jawa29
0
 
mbizupCommented:
ie:

>> Dim varTextDataA As String

Should be:
Dim vartextdataB As Variant

Also, this intermediate step is unnecessary:

varTextDataA = srn
vartextdataB = invoice2
vartextdataC = BuyerName2
vartextdataD = BuyerAdd1
vartextdataE = BuyerAdd2
vartextdataF = BuyerAdd3

Open in new window


You can set the fields directly from the textboxes:

rst!SalesRecordNumber= srn
  rst!InvoiceDate = invoice2

etc.

With the latter method, nulls are not an issue - as long as the underlying field allows nulls

0
 
ridgejasonAuthor Commented:
Thank you, I knew it would be something simple.
0
 
mbizupCommented:
Glad to help out.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now