Wrox Programmer Forums
|
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 software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 22nd, 2005, 03:25 PM
Authorized User
 
Join Date: Dec 2004
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
 
Old March 22nd, 2005, 04:26 PM
Friend of Wrox
 
Join Date: Mar 2004
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
 
Old March 22nd, 2005, 04:28 PM
Friend of Wrox
 
Join Date: Mar 2004
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
 
Old March 23rd, 2005, 09:56 AM
Friend of Wrox
 
Join Date: Nov 2004
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.
 
Old March 23rd, 2005, 10:06 AM
Friend of Wrox
 
Join Date: Nov 2004
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
 
Old March 23rd, 2005, 10:33 AM
Friend of Wrox
 
Join Date: Mar 2004
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
 
Old March 23rd, 2005, 12:07 PM
Authorized User
 
Join Date: Dec 2004
Posts: 43
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Cheers
Tony





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





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.