By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,701 Members | 1,928 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,701 IT Pros & Developers. It's quick & easy.

VBA If Table Field Value equals Then

P: n/a
I am just getting started with VBA and I'd appreciate any help anyone
can offer here. I am trying to use VBA to look through the "Curr Year"
column in table "tblYear" and check whether the value "2006" is in any
of the fields for that column. Here's the code I have so far...

Private Sub UpCurYr_Click()

Dim db As Database
Dim tdf As TableDef
Dim X As String

Set db = CurrentDb()
Set tdf = db.TableDefs("tblYear")
Set X = 2006

If tdf.Fields("Curr Year") = X Then
MsgBox ("You got it")
End If

End Sub

The MsgBox is just to check if the code works. If anyone knows of a
good VBA introductory examples page please let me know.

Thanks everyone...

Oct 6 '06 #1
Share this Question
Share on Google+
6 Replies


P: n/a
Unless I'm missing something, why aren't you using a query? I'd use
only for stuff I can't do with plain SQL. SQL is _much_ faster at
doing data manipulation than VB... and easier to write.

Oct 6 '06 #2

P: n/a
I'm sorry I should have mentioned I intend to remove the msgbox line
and replace it with a DoCmd.RunSql statement. The sql will run as an
append query and and take last years values and give them this year's
date. I want to avoid having to run two queries inorder to get the
process done. There are already 7 queries manipulating the data. I just
need this if statement to check if the new append query has already put
this years date in yet.

Thanks for your help,

Oct 6 '06 #3

P: n/a

hm*****@hartford.edu wrote:
I'm sorry I should have mentioned I intend to remove the msgbox line
and replace it with a DoCmd.RunSql statement. The sql will run as an
append query and and take last years values and give them this year's
date. I want to avoid having to run two queries inorder to get the
process done. There are already 7 queries manipulating the data. I just
need this if statement to check if the new append query has already put
this years date in yet.

Thanks for your help,
I'd go with
DoCmd.SetWarnings False
DoCmd.OpenQuery "query1"
DoCmd.OpenQuery "query2"
DoCmd SetWarnings True

Oct 6 '06 #4

P: n/a
I'm sorry I must not be making my point, and thank you for your help.

I want to use VBA to check whether or not an append query has been run.
I believe the way to check for this is to check for the new values in
the table. So if I'm updating information for 2006, 2006 would now be
in the record. So this if statement would check the "Curr Year" column,
see that the value 2006 is already in the column and end it's
subroutine.

If
2006 exists in Column

Then
Don't run Append Query

Else

Run Append Query

Thanks again

Oct 6 '06 #5

P: n/a
I'm sorry I must not be making my point, and thank you for your help.

I want to use VBA to check whether or not an append query has been run.
I believe the way to check for this is to check for the new values in
the table. So if I'm updating information for 2006, 2006 would now be
in the record. So this if statement would check the "Curr Year" column,
see that the value 2006 is already in the column and end it's
subroutine.

If
2006 exists in Column

Then
Don't run Append Query

Else

Run Append Query

Thanks again

Oct 6 '06 #6

P: n/a

if isDate(dLookup("[Curr Year]", "tblYear", "[Curr Year]='2006'")) then
msgbox("You got it")
endif
<hm*****@hartford.eduwrote in message
news:11**********************@k70g2000cwa.googlegr oups.com...
I am just getting started with VBA and I'd appreciate any help anyone
can offer here. I am trying to use VBA to look through the "Curr Year"
column in table "tblYear" and check whether the value "2006" is in any
of the fields for that column. Here's the code I have so far...

Private Sub UpCurYr_Click()

Dim db As Database
Dim tdf As TableDef
Dim X As String

Set db = CurrentDb()
Set tdf = db.TableDefs("tblYear")
Set X = 2006

If tdf.Fields("Curr Year") = X Then
MsgBox ("You got it")
End If

End Sub

The MsgBox is just to check if the code works. If anyone knows of a
good VBA introductory examples page please let me know.

Thanks everyone...

Oct 6 '06 #7

This discussion thread is closed

Replies have been disabled for this discussion.