Using a variable to make program easy to use for multiple fields

G

Guest

The below program works for everything except the update. I am trying to
make the program apply to various tables and fields.

Thanks for your help

Dim arg1 As Variant
Dim arg2 As Variant
arg2 = "test"
Dim dbb01 As DAO.Database
Dim rstb01 As DAO.Recordset
Set dbb01 = CurrentDb()
Set rstb01 = dbb01.OpenRecordset(arg2, dbOpenDynaset)
arg1 = [rstb01]![CUSTOMER_ID]

rstb01.MoveFirst
If (rstb01.EOF) Then GoTo bo1 Else

Dim var1 As Variant
Dim var2 As Variant
Dim counter As Integer
counter = 12
var1 = [arg1]
GoTo b02

b03:
rstb01.MoveNext
If (rstb01.EOF) Then GoTo bo1 Else
counter = 12
var1 = arg1
b02:
rstb01.Edit
counter = counter - 1
var2 = Left(var1, 1)
If var2 = 0 Then var1 = Right(arg1, counter) Else GoTo b03
arg1 = var1
rstb01.Update ' Does not
update the table
GoTo b02
bo1:
rstb01.Close
dbb01.Close
 
B

Brian Coiley

vtj said:
The below program works for everything except the update. I am trying to
make the program apply to various tables and fields.

Thanks for your help

Dim arg1 As Variant
Dim arg2 As Variant
arg2 = "test"
Dim dbb01 As DAO.Database
Dim rstb01 As DAO.Recordset
Set dbb01 = CurrentDb()
Set rstb01 = dbb01.OpenRecordset(arg2, dbOpenDynaset)
arg1 = [rstb01]![CUSTOMER_ID]

rstb01.MoveFirst
If (rstb01.EOF) Then GoTo bo1 Else

Dim var1 As Variant
Dim var2 As Variant
Dim counter As Integer
counter = 12
var1 = [arg1]
GoTo b02

b03:
rstb01.MoveNext
If (rstb01.EOF) Then GoTo bo1 Else
counter = 12
var1 = arg1
b02:
rstb01.Edit
counter = counter - 1
var2 = Left(var1, 1)
If var2 = 0 Then var1 = Right(arg1, counter) Else GoTo b03
arg1 = var1
rstb01.Update ' Does not
update the table
GoTo b02
bo1:
rstb01.Close
dbb01.Close
Click to expand...

That's because you have not changed any values in the recordset!

I'm guessing from your code that what you are trying to update is the value
of CUSTOMER_ID, but you seem to have misunderstood the concept of a
variable. The statement "arg1 = [rstb01]![CUSTOMER_ID]" created, in arg1, a
COPY of the CUSTOMER_ID, it did NOT create an alias for or a pointer to
CUSTOMER_ID.

Instead of "arg1 = var1" before the Update, try:

[rstb01]![CUSTOMER_ID] = var1

Incidentally, using GoTo to control the execution of your code is just about
the most revolting thing a programmer can do, for reasons that you have
perfectly demonstrated: in only about 20 lines of code, you have created a
procedure which is all-but-unreadable (as you will discover for yourself
when you come back to it in about 6 months). I suggest that you learn about
other control-of-flow constructs such as If...Then, Do...Loop, For...Next
and Select Case. Most people consider that the only acceptable use of GoTo
in VBA is in error handling.
 
G

Guest

Thanks Brian!! Apparantly my question should have been how to create the
alias or pointer so that I could change the field name once in the program
and have it work in various places. That would allow me to change the
program very quickly and yet use it in multiple places or even pass the name
from a form and call the program. If you could help with that I would
appreciate it.

As for the GoTo statements, I'm an old COBOL programer that has found VBA is
not very good at controlling itself - knowing when it has hit a null or blank
or value for instance - and so controll the flow myself. The full set of VB
may be a lot better than VBA at handling those kinds of things but I've had
too many messes created when VBA doesn't know what it is doing to trust it
completely.

Thanks for your help.
Jerry

Brian Coiley said:
vtj said:
The below program works for everything except the update. I am trying to
make the program apply to various tables and fields.

Thanks for your help

Dim arg1 As Variant
Dim arg2 As Variant
arg2 = "test"
Dim dbb01 As DAO.Database
Dim rstb01 As DAO.Recordset
Set dbb01 = CurrentDb()
Set rstb01 = dbb01.OpenRecordset(arg2, dbOpenDynaset)
arg1 = [rstb01]![CUSTOMER_ID]

rstb01.MoveFirst
If (rstb01.EOF) Then GoTo bo1 Else

Dim var1 As Variant
Dim var2 As Variant
Dim counter As Integer
counter = 12
var1 = [arg1]
GoTo b02

b03:
rstb01.MoveNext
If (rstb01.EOF) Then GoTo bo1 Else
counter = 12
var1 = arg1
b02:
rstb01.Edit
counter = counter - 1
var2 = Left(var1, 1)
If var2 = 0 Then var1 = Right(arg1, counter) Else GoTo b03
arg1 = var1
rstb01.Update ' Does not
update the table
GoTo b02
bo1:
rstb01.Close
dbb01.Close
Click to expand...

That's because you have not changed any values in the recordset!

I'm guessing from your code that what you are trying to update is the value
of CUSTOMER_ID, but you seem to have misunderstood the concept of a
variable. The statement "arg1 = [rstb01]![CUSTOMER_ID]" created, in arg1, a
COPY of the CUSTOMER_ID, it did NOT create an alias for or a pointer to
CUSTOMER_ID.

Instead of "arg1 = var1" before the Update, try:

[rstb01]![CUSTOMER_ID] = var1

Incidentally, using GoTo to control the execution of your code is just about
the most revolting thing a programmer can do, for reasons that you have
perfectly demonstrated: in only about 20 lines of code, you have created a
procedure which is all-but-unreadable (as you will discover for yourself
when you come back to it in about 6 months). I suggest that you learn about
other control-of-flow constructs such as If...Then, Do...Loop, For...Next
and Select Case. Most people consider that the only acceptable use of GoTo
in VBA is in error handling.
Click to expand...
 
B

Brian

vtj said:
Thanks Brian!! Apparantly my question should have been how to create the
alias or pointer so that I could change the field name once in the program
and have it work in various places. That would allow me to change the
program very quickly and yet use it in multiple places or even pass the name
from a form and call the program. If you could help with that I would
appreciate it.

As for the GoTo statements, I'm an old COBOL programer that has found VBA is
not very good at controlling itself - knowing when it has hit a null or blank
or value for instance - and so controll the flow myself. The full set of VB
may be a lot better than VBA at handling those kinds of things but I've had
too many messes created when VBA doesn't know what it is doing to trust it
completely.

Thanks for your help.
Jerry

:
Click to expand...

OK, I see what you are trying to do. Let us suppose, then, that you had the
name of the field in a variable called, say, arg3, in much the same way that
you have the name of the table in arg2. You already have the value in arg1,
so the assignment you want to perform, before the rstb01.Update, would look
like this:

rstb01(arg3) = arg1

Regarding the GoTo's, I too am an old COBOL programmer, and I considered
GoTo just as disgusting then. I don't understand at all what you say about
VBA's control-of-flow statements. I, and millions of others, find that they
behave just fine. If you could post a specific problem here, I'm sure
someone could tell you what you are doing wrong. Incidentally, the
control-of-flow statements in VBA are identical to those in VB6.
 
G

Guest

Hi Brian,

You wanted an example of VBA not knowing what it is doing. Here it is:

Debug.Print
DoCmd.SetWarnings False

Dim dbb01 As DAO.Database
Dim rstb01 As DAO.Recordset
Set dbb01 = CurrentDb()
Set rstb01 = dbb01.OpenRecordset("test", dbOpenDynaset)
Dim ssn As Variant
rstb01.MoveFirst
If (rstb01.EOF) Then GoTo bo1 Else

Dim var1 As Variant
var1 = [rstb01]![ssn]
GoTo b02

b03:
rstb01.MoveNext
If (rstb01.EOF) Then GoTo bo1 Else
counter = 0
var1 = [rstb01]![ssn]
Debug.Print var1
b02:
rstb01.Edit
If [rstb01]![ssn] = Null Then GoTo b03 'Tried using var1 instead of
[rstb01]![ssn]
'Tried defining(DIM) the items as String

It does not matter what I do VBA will not recognize a "null" field. It
always tells me that it is an improper use of null when all I'm trying to do
is determine if a field is null.

Suggestions????

Jerry
 
B

Brian

vtj said:
Hi Brian,

You wanted an example of VBA not knowing what it is doing. Here it is:

Debug.Print
DoCmd.SetWarnings False

Dim dbb01 As DAO.Database
Dim rstb01 As DAO.Recordset
Set dbb01 = CurrentDb()
Set rstb01 = dbb01.OpenRecordset("test", dbOpenDynaset)
Dim ssn As Variant
rstb01.MoveFirst
If (rstb01.EOF) Then GoTo bo1 Else

Dim var1 As Variant
var1 = [rstb01]![ssn]
GoTo b02

b03:
rstb01.MoveNext
If (rstb01.EOF) Then GoTo bo1 Else
counter = 0
var1 = [rstb01]![ssn]
Debug.Print var1
b02:
rstb01.Edit
If [rstb01]![ssn] = Null Then GoTo b03 'Tried using var1 instead of
[rstb01]![ssn]
'Tried defining(DIM) the items as String

It does not matter what I do VBA will not recognize a "null" field. It
always tells me that it is an improper use of null when all I'm trying to do
is determine if a field is null.

Suggestions????

Jerry
Click to expand...

It isn't the expression "[rstb01]![ssn] = Null" that gives an "Invalid use
of null" error. Comparing anything to Null always returns False.
Therefore, the "GoTo b03" branch is never executed, with the result that
whatever code follows is always executed, even if rstb01!ssn is null. So,
if you are then assigning rstb01!ssn to anything other than a variant, you
will indeed get an "Invalid use of Null" error.

This isn't because VBA doesn't know what it's doing, it's because YOU don't
know what you are doing. To test something for null, you need to use the
IsNull function e.g.:

If IsNull(rstb01!ssn) Then...

I repeat, comparing anything to Null ALWAYS returns False, and to test for
Null you use the IsNull function. No inconsistency, no confusion, no need
to bitch at the language: you simply need to learn it properly!
 
B

Brian

vtj said:
Hi Brian,

You wanted an example of VBA not knowing what it is doing. Here it is:

Debug.Print
DoCmd.SetWarnings False

Dim dbb01 As DAO.Database
Dim rstb01 As DAO.Recordset
Set dbb01 = CurrentDb()
Set rstb01 = dbb01.OpenRecordset("test", dbOpenDynaset)
Dim ssn As Variant
rstb01.MoveFirst
If (rstb01.EOF) Then GoTo bo1 Else

Dim var1 As Variant
var1 = [rstb01]![ssn]
GoTo b02

b03:
rstb01.MoveNext
If (rstb01.EOF) Then GoTo bo1 Else
counter = 0
var1 = [rstb01]![ssn]
Debug.Print var1
b02:
rstb01.Edit
If [rstb01]![ssn] = Null Then GoTo b03 'Tried using var1 instead of
[rstb01]![ssn]
'Tried defining(DIM) the items as String

It does not matter what I do VBA will not recognize a "null" field. It
always tells me that it is an improper use of null when all I'm trying to do
is determine if a field is null.

Suggestions????

Jerry
Click to expand...

BTW, here is the smart way of implementing your loop:

Dim dbb01 As DAO.Database
Dim rstb01 As DAO.Recordset
Set dbb01 = CurrentDb()
Set rstb01 = dbb01.OpenRecordset("test", dbOpenDynaset)

Do Until rstb01.EOF
If Not IsNull(rstb01!ssn) Then
'Do some stuff here
End If
rstb01.MoveNext
Loop
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Make all variable values in a Array equal to 0 8
Ability to place all the documents being exported into a .ZIP file 3
Code Stops Due to No Values In Query 5
Passing a Concatenated String to a Field on a Report 2
How to use the name for an array by using a variable? 6
Tracking changes to a form's fields---still searching for an answe 6
Posting a dataset to a SQL table using VBA 0
comparing the date entered to a form and the date field in a table 2

Top