473,320 Members | 2,202 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

VBA If Table Field Value equals Then

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
6 20734
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
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

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
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
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

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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Bob | last post by:
How do I extract the value of a table field and save it to a variable of similar data type in VBA ? Thank you in advance, Bob.
1
by: MLH | last post by:
Can someone tell me what I'm doing wrong in this query? UPDATE tblEventsSituationsIncidents SET tblEventsSituationsIncidents.ES01 = +1; I'm trying to update the value in table field and am...
2
by: Neo | last post by:
I have a table Contacts with a field "Male" which is of type Bit (SQL Server). I've got two radio buttons in a panel, & one of them is bound to the above field, (so that one can see if the...
3
by: MLH | last post by:
Generally, I do not monkey with renaming controls on forms whose name, by default, matches the name of their related table fields. But I noticed the following today If IsNull(Me!VColor) Then...
2
by: MLH | last post by:
Fields in MyTable: PostID PostDate RollQtyXfer RollDenomination RollCount37 RollCount23
2
by: David - Australia | last post by:
G'day from Australia, I'm hoping some bright spark may be able to help me with this one. I'm sure that it can be done, I've just hit a wall with it. So I'm opening it up. I'm storing student...
1
by: lizarraga | last post by:
I have a table this table sep 2007 = 1.7890 oct 2007 = 1.8952 nov 2007 = 1.8970 in the field (date) sep 2007 the problem is and and other field get 1.7890
2
by: timleonard | last post by:
How do you reference the Table:Field to use in a file path? I have been trying the code below I would like to reference a Tablel:Field to use in the path instead of hard coding the file name,...
1
by: lipton | last post by:
how can i compare a mysql table feild value with selected redio button value with php
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.