Solved

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

Posted on 2011-09-29
5
440 Views
Last Modified: 2013-11-08
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
Comment
Question by:ridgejason
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 36814695
For anything that allows Nulls, declare them as Variant not String.
0
 
LVL 8

Expert Comment

by:jawa29
ID: 36814704
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
 
LVL 61

Expert Comment

by:mbizup
ID: 36814728
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
 

Author Comment

by:ridgejason
ID: 36814776
Thank you, I knew it would be something simple.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36814984
Glad to help out.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question