-
Jan 23rd, 2012, 06:38 PM
#1
Thread Starter
New Member
[RESOLVED] Very simple Access 2007 VBA question concerning variables
I'm having the hardest time figuring out how to do something very simple. I'm simply trying to make the below code easier to manage by changing this:
Code:
Private Sub firstname_AfterUpdate()
If IsNull(Me.backofficeID) Then
If DCount("*", "backofficeaccounts") = 0 Then
backofficeID = 1
Else
backofficeID = DMax("backofficeID", "backofficeaccounts") + 1
End If
End If
End Sub
To this:
Code:
Private Sub firstname_AfterUpdate()
Dim var_field As String
Dim var_table As String
var_field = "backofficeID"
var_table = "backofficeaccounts"
If IsNull(Me.backofficeID) Then
If DCount("*", var_table) = 0 Then
var_field = 1
Else
var_field = DMax(var_field, var_table) + 1
End If
End If
End Sub
Basically, just replacing references to the backofficeID field and the backofficeaccounts table with the variables var_field and var_table that I can update as needed when I reuse this formula. I know for a fact that these variables work when I use them in the DCount and DMax functions, but not when I use them to assign them with new values such as the number 1 in the first statement. Why doesn't this work?
-
Jan 23rd, 2012, 11:26 PM
#2
Member
Re: Very simple Access 2007 VBA question concerning variables
One thing I see is var_field getting assigned a value of 1 (integer) instead of "1" (string)... working with excel I know that won't work because a string can't be an integer...
I'm not familiar with access, but I guessed from your variable names that there might be a field object type... googled... and there is; it has a value and a name. That's how the Dcount function is able to use it I'm guessing...
Try setting your variables to field objects instead of strings and see if that works.
Here's a link to the field object page:
http://msdn.microsoft.com/en-us/libr...ffice.12).aspx
-
Jan 23rd, 2012, 11:26 PM
#3
Addicted Member
Re: Very simple Access 2007 VBA question concerning variables
I'm assuming your DCount and Dmax functions are something you created. If so, then they are probably expecting Variants, UDT's, or something other than a string. The var_field, while holding the name, is technically a String. Can you post the declaration parts of the D functions?
My attempt at a thought provoking sig.
* Remove ALL assumptions. You'll probably find your problem.
* New to the forums? READ THIS! The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft
* Learn how to fish!
* Subscribing to every thread I post in is like having a book mark in my mind. Every now and then I get to review a fond memory.
* If you find my post helpful do me a 15 second favor and RATE it. (Rate Post. Over there <----)
* Please mark all resolved threads as such. It makes searching for fixes SO much easier!
* Have fun. We aren't on this rock for long, enjoy it.
* Sometimes I'm wrong. When I am I admit it. Never am I malicious. If reading my post and you think otherwise, then it MAY be tongue in cheek. Read it again.
-
Jan 23rd, 2012, 11:53 PM
#4
Member
Re: Very simple Access 2007 VBA question concerning variables
The syntax for the DCount function is:
DCount ( expression, domain, [criteria] )
expression is the field that you use to count the number of records.
domain is the set of records. This can be a table or a query name.
criteria is optional. It is the WHERE clause to apply to the domain.
http://www.techonthenet.com/access/f...ain/dcount.php
so perhaps this?
Code:
Dim var_field As Field
Dim var_table As Table
-
Jan 24th, 2012, 09:59 AM
#5
Thread Starter
New Member
Re: Very simple Access 2007 VBA question concerning variables
fumalicious
I've tried setting up the var_field and var_table variables as a Field and Table object per the below code, but neither works. For one, the Table object doesn't exist. The closest I can get is the TableDef object. Still no positive results.
Code:
Dim var_field As Field
Dim var_table As TableDef
Darren M.
The DCount and DMax functions are built-in Access 2007 functions that expect at least two strings, the first string is the field to be operated on, and the second is the table containing that field.
A new approach...
In order to narrow down the possibilities, I changed the procedure to use the following code:
Code:
Private Sub firstname_AfterUpdate()
Dim var_field As String
Dim var_table As String
var_field = "backofficeID"
var_table = "backofficeaccounts"
If IsNull(Me.backofficeID) Then
If DCount("*", var_table) = 0 Then
backofficeID = 1
Else
backofficeID = DMax(var_field, var_table) + 1
End If
End If
End Sub
This code works without any problems. Apparently the DMax and DCount functions have no problem accepting the var_field and var_table string variables. The only portions that have problems are the two direct references to the backofficeID field in the table. Both times backofficeID are used, they are used to set the field's value to 1 or the max value plus 1, depending on how many rows there are in the table. To test further, I replaced backofficeID with a new variable, var_test as shown in the below code.
Code:
Private Sub firstname_AfterUpdate()
Dim var_field As String
Dim var_table As String
Dim var_test
var_field = "backofficeID"
var_table = "backofficeaccounts"
var_test = backofficeID
If IsNull(Me.backofficeID) Then
If DCount("*", var_table) = 0 Then
var_test = 1
Else
var_test = DMax(var_field, var_table) + 1
End If
End If
End Sub
Unfortunately this code doesn't work either. It doesn't return an error. It just doesn't do anything. FYI: Setting "Dim var_test As Field" highlights "var_test = backofficeID" and returns the error: "Object or variable with Block variable not set".
The question is, how to define var_test?
-
Jan 24th, 2012, 02:29 PM
#6
Member
Re: Very simple Access 2007 VBA question concerning variables
Code:
Dim var_field As Field
var_field = backofficeID
Dim var_field to a Field means it's an object, so it needs the keyword "Set" (to create an instance of it) before the variable can be used.
like this:
Code:
Dim var_field As Field
Set var_field = backofficeID
Last edited by fumalicious; Jan 24th, 2012 at 02:35 PM.
-
Jan 25th, 2012, 10:21 AM
#7
Frenzied Member
Re: Very simple Access 2007 VBA question concerning variables
try simply dim as variant
or do not dim at all
for test purposes
and then toss a load of stops so you can asses the values
Last edited by incidentals; Jan 25th, 2012 at 10:26 AM.
-
Jan 30th, 2012, 03:30 PM
#8
Thread Starter
New Member
Re: Very simple Access 2007 VBA question concerning variables
I've solved the problem. Here is the working code:
Code:
Private Sub firstname_AfterUpdate()
var_field = "backofficeID"
var_table = "backofficeaccounts"
Set var_test = backofficeID
If IsNull(Me.backofficeID) Then
If DCount("*", var_table) = 0 Then
var_test.Value = 1
Else
var_test.Value = DMax(var_field, var_table) + 1
End If
End If
End Sub
Notes:
- It's not necessary to use Dim to assign variable types.
- It IS necessary to use Set to assign the var_test variable to the backofficeID object.
- The key is using .Value to set the var_test object's new value rather than just referencing var_test directly. I'm not 100% sure why this works, but I believe it may have something to do with var_test being an object instead of just another variable.
-
Jan 30th, 2012, 04:07 PM
#9
Addicted Member
Re: Very simple Access 2007 VBA question concerning variables
Originally Posted by Novan Leon
I've solved the problem. Here is the working code:
Notes:
- It's not necessary to use Dim to assign variable types.
- It IS necessary to use Set to assign the var_test variable to the backofficeID object.
- The key is using .Value to set the var_test object's new value rather than just referencing var_test directly. I'm not 100% sure why this works, but I believe it may have something to do with var_test being an object instead of just another variable.
Novan, it's good to see that you worked through your problems! Congrats! However, put this statement at the top of all your modules..
Then you MUST declare your variables. This is a coding best practice that many will strongly suggest you follow. In VB/VBA you can turn this on as an option and have it automatically created. Just look under options.
My attempt at a thought provoking sig.
* Remove ALL assumptions. You'll probably find your problem.
* New to the forums? READ THIS! The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft
* Learn how to fish!
* Subscribing to every thread I post in is like having a book mark in my mind. Every now and then I get to review a fond memory.
* If you find my post helpful do me a 15 second favor and RATE it. (Rate Post. Over there <----)
* Please mark all resolved threads as such. It makes searching for fixes SO much easier!
* Have fun. We aren't on this rock for long, enjoy it.
* Sometimes I'm wrong. When I am I admit it. Never am I malicious. If reading my post and you think otherwise, then it MAY be tongue in cheek. Read it again.
-
Jan 31st, 2012, 11:35 AM
#10
Thread Starter
New Member
Re: Very simple Access 2007 VBA question concerning variables
Originally Posted by Darren M.
Novan, it's good to see that you worked through your problems! Congrats! However, put this statement at the top of all your modules..
Then you MUST declare your variables. This is a coding best practice that many will strongly suggest you follow. In VB/VBA you can turn this on as an option and have it automatically created. Just look under options.
I will definitely do this from now on. Thanks for the help.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|