Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old March 22nd, 2005, 03:25 PM
Authorized User
 
Join Date: Dec 2004
Location: Belfast, Antrim, United Kingdom.
Posts: 43
Thanks: 0
Thanked 0 Times in 0 Posts
Default Updating table

Hi

I'm trying to update a table via an SQL statement. The SQL statement consists of sevral variables from a form. When I fill all the text boxes in and therefore populate all the variables the table update works fine. However when I leave one or more fields blank, the table does not update. Anyone any ideas?

Cheers
Tony
__________________
Cheers
Tony
Reply With Quote
  #2 (permalink)  
Old March 22nd, 2005, 04:26 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Do something like this since your query is not passing nulls.

You need to check EACH text box or combo box etc that is passing data to your update query for Null. If it is null, put in some value (I put "Null"), and if it is not null, then pass the value.

IOW, you need to check each value before you pass it to a variable which you pass to the query string, and not pass the value directly to the query string without checking or without a variable.

'=====
Dim stText1 As String
'...

If IsNull(Me.txtMyTextBox) Then
   stText1 = "Null"
   Else
   stText1 = Me.txtMyTextBox
End If
'...
'=====

HTH


mmcdonal
Reply With Quote
  #3 (permalink)  
Old March 22nd, 2005, 04:28 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Ooops, then pass stText1 to the UPDATE query string as a field value.

mmcdonal
Reply With Quote
  #4 (permalink)  
Old March 23rd, 2005, 09:56 AM
Friend of Wrox
 
Join Date: Nov 2004
Location: Seattle, WA, .
Posts: 248
Thanks: 0
Thanked 1 Time in 1 Post
Default

Actually I wouldn't do what mmcdonal suggests.

"Null" is NOT the same as Null. Why put values in your table if you don't have data for them?

I would suggest that you check your table constraints to see if the fields that you are not entering require a value. If so, then you should put edits on the form to force entry.

I do see where mmcdonal's solution would work.

If you are building the SQL string based on the field values using something like:

sql = sql & "Field1 = " & me.field1

This will make a "valid" SQL statement where you didn't have one without change the value of the field to "Null".

However, if you're using

sql = sql & "Field1 = """ & me.field1 & """"

as you should be doing to make sure you build a valid SQL string if the value of me.field1 has a space in it, your table will store the word "Null" if the field is not entered on the form.

In short, since you're building the SQL statement yourself, make sure it has valid SQL syntax.

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org

P.S. Actually, on further review I notice that I would do what mmcdonal suggests. But there is a way that doesn't require using an extra variable which I describe in a later post. Also, mmcdonal's way doesn't cover building a valid SQL if your field has a space or special character in it. Sorry mmcdonal.
Reply With Quote
  #5 (permalink)  
Old March 23rd, 2005, 10:06 AM
Friend of Wrox
 
Join Date: Nov 2004
Location: Seattle, WA, .
Posts: 248
Thanks: 0
Thanked 1 Time in 1 Post
Default

Each field you add to your SQL statement should have something like this:

sql = sql & iif(isnull(me.field1),"Null","""" & me.field1 & """") ' for text fields

sql = sql & iif(isnull(me.field1),"Null","#" & me.field1 & "#") ' for date fields

sql = sql & iif(isnull(me.field1),"Null",me.field1) ' for numeric fields

or you could do it the long way...

If isnull(me.field1) then ' a text field
    sql = sql & "Null"
else
    sql = sql & """" & me.field1 & """"
endif

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org
Reply With Quote
  #6 (permalink)  
Old March 23rd, 2005, 10:33 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

I said do something LIKE this, and "I put "Null" just to demonstrate a place holder. The upshot is that the UPDATE is invalid if table validation is not adhered to.

mmcdonal
Reply With Quote
  #7 (permalink)  
Old March 23rd, 2005, 12:07 PM
Authorized User
 
Join Date: Dec 2004
Location: Belfast, Antrim, United Kingdom.
Posts: 43
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks a lot chaps.....that should do the trick

Cheers
Tony
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Updating one table with data from another table dirtdog22 Access VBA 1 January 21st, 2008 04:41 PM
Updating Table gregalb SQL Server 2000 1 June 2nd, 2007 04:39 PM
Updating table every now and then rtr1900 Classic ASP Databases 2 December 2nd, 2005 03:12 AM
Instant updating of table Hudson40 Access VBA 2 January 26th, 2005 11:52 AM
updating table markhardiman Classic ASP Databases 2 September 29th, 2004 12:35 PM



All times are GMT -4. The time now is 02:33 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.