Getting Started with VBA and Access

  • Thread starter Thread starter John Bishop
  • Start date Start date
J

John Bishop

I have been poring over the built-in Help files for MS Access 2000, VBA and
various online help facilities for hours and frankly my head is spinning. I
see a wide variety of solutions to many complex problems, but I seem to be
unable to get any traction with the VBA code necessary to accomplish the
very simplest of tasks. Access and VBA are so "feature rich" and the
programming alternatives are so many and varied that I cant seem to gain the
grasp of how to take the very first step.

Suppose that I have a MS Access database called "Test.mdb" containing a
single table called "TestTable", which table contains three fields called
TxtField1, TxtField2 and NumField3. This database contains n existing
records with valid useful data for TxtField1 and TxtField2.

I now want to populate each and every record of TestTable with a value for
NumField3. I already have a working function that calculates the value to
be inserted. For the sake of simplicity here, suppose that I merely wanted
to set the value of NumField3 for each record to a certain constant, for
example "10.5". The text values of the other fields are to remain
unaltered.

What code would be necessary to open this table, to sequence through the
records and update only the value of the single field NumField3 to 10.5 for
each record.

(Obviously, this is trivial to do using simply a Query, but a Query will not
give the correct individual values to each element using the function I
intend to use to actually populate the Table.)

I sure that I must seem to be a DUNCE, but I think that if I could simply
see a simple example as to how to accomplish the task described above, I
would be away to the races producing useful code!
 
Sorry to hit you with yet more alternatives, but there are at least four
different ways to accomplish this. It's entirely possible that it can be
done with a query, and if so, that is likely to be the most efficient way to
do it. If it really can't be done with a query, then you can do it by
looping through a recordset. Either one of these tasks - executing a query
or looping through a recordset - can be accomplished using either DAO or
ADO, hence four alternatives. Here are some examples ...

Public Sub Example1()

'Using a query

Dim strSQL As String

strSQL = "UPDATE tblTest SET Long1 = IIf(Text1 = 'three', 2, 3)"

'Using ADO
'CurrentProject.Connection.Execute strSQL

'Using DAO
CurrentDb.Execute strSQL

End Sub

Public Sub Example2()

'Using an ADO recordset

Dim rst As ADODB.Recordset
Dim strSQL As String

strSQL = "SELECT Text1, Long1 FROM tblTest"
Set rst = New ADODB.Recordset
With rst
.ActiveConnection = CurrentProject.Connection
.CursorLocation = adUseClient
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open strSQL
Do Until .EOF
If .Fields("Text1") = "three" Then
.Fields("Long1") = 4
Else
.Fields("Long1") = 5
End If
.MoveNext
Loop
.Close
End With

End Sub

Public Sub Example3()

'Using a DAO recordset

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT Text1, Long1 FROM tblTest"
Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)
With rst
Do Until .EOF
.Edit
If .Fields("Text1") = "three" Then
.Fields("Long1") = 6
Else
.Fields("Long1") = 7
End If
.Update
.MoveNext
Loop
.Close
End With

End Sub

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
Keep in mind there are always multiple ways to solve a
problem. Also, you might not want to solve it in this
manner, as usually you do not want calculated fields
stored in the database. There are always exceptions, so
here is sample code:



Sub UpdateRecords()
'We'll use an ADO Recordset
Dim rst As New ADODB.Recordset
'We have to open a SQL statement.
'Also, CurrentProject.Connection says we want to use
the current database
'Check the help for the last 2 options
rst.Open "Select * from Table1",
CurrentProject.Connection, adOpenDynamic, adLockOptimistic
'Start a loop for each record. After you get past the
last record, rst.EOF will be true
Do Until rst.EOF
'In the Recordset Field collection, set the value
property to 10.5
rst("Field3").Value = 10.5
'Commit the change. Not really necessary, but
good to see when it happens
rst.Update
'Move to the next record
rst.MoveNext
'Loop
Loop
'Close the recordset
rst.Close
'Destroy the object variable
Set rst = Nothing
'Done
End Sub



Chris Nebinger
 
To set all values in the table, for field3 to 10.5, he code would be:


Currentdb.Execute "update TestTable set Field3 = 10.5"

That is it...one line of code!

Of couse, as you mentioned, the above is a bit simple...since you next
question is how then can I work with other fields?

Lets say, we wanted field3 to be 4 x field1

CurrentDb.Execute "update TestTable set Field3 = (Field1 * 4)"
 
Albert, I'm new to this forum and have a similar situation to John's only I
don't want to update a field in a table I want to build a new field in a
query, the mailing label for exporting or displaying in a form or report. I
have a file for members that includes companies and persons. So I want to
build a field in a query that checks the value of the record's afflication
code if it is "corp/fdn" I want the mail label to be the contents of the
Company field if it is any thing else I want to build the label using the
Prefix,first,middle and last names for the person.
 
Albert, I'm new to this forum and have a similar situation to John's only
I
don't want to update a field in a table I want to build a new field in a
query
Click to expand...

Gee, that is VERY different. You likely should have started a new thread
here.

Further, you don't need any VBA, nor code to accomplish what you need.

And, further, you are not trying to build, or transfer data to new fields.

I will take a stab at this..but we kind of should have started a new
thread...as we are going to confuse the issue here a bit!!

I would fire up the query builder. I would create an expression that returns
the full name, like:


FullName: ( ([Prefix] + " ") & ([FirstName] + " ") & ([Middle] + " ") &
([LastName]) )

If any of the Prefix, FirstName, Middle are blank, then the " " (space)
after the name will be discarded with the above expressions.

Ok, so, the above gives use a column in the query builder that returns a
full name (I would flip from design mode to datasheet mode to take a quick
look and see if the column is ok...).

Then, build another column in the query builder of:

TName: ( IIf( [Afflication] = 'corp/fnd',[Company], [FullName]) )

Thus, you now can base the report on this query, are free to use the full
name collum and the TName column in your report as you please.


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.attcanada.net/~kallal.msn
=
 
Back
Top