Briefly, Dim (I think it is short for Dimension, but I'm not sure) means to
define a bit of text (a variable) as something. For instance:
Dim strMsg as String
strMsg = "Hello"
MsgBox strMsg
will produce a message box with the message "Hello" when the code is run.
The three lines above are the same as:
MsgBox "Hello"
It is not necessarily helpful in the simple example above, but sometimes you
need to use the same text several times in a procedure, or it is a long
message or one that can vary depending on the circumstances.
In other cases you may have:
Dim ctl as Control
For Each ctl in Me.Controls
If ctl.Tag = "D" Then
ctl.Enabled = False
End If
Next ctl
This is telling Access to look at each control in the form. If it's Tag
property is set to "D", disable the control (set its Enabled property to
False).
In your example, it's a little more subtle. RecordsetClone is essentially a
copy of the form's recordset (the group of records the form is using as its
Record Source) that Access can navigate independently of the form. The code
is telling Access to search through the RecordsetClone for the first record
in which certain criteria are met. When that record is found, look at its
Bookmark (Access assigns each record a unique Bookmark behind the scenes),
find the record in the form's Recordset (the records the form is actually
using) with the same Bookmark, and go to that record.
Object is harder to describe, at least for me. Help is not especially
helpful on the topic. It may be enough for now to know you are telling
Access to "think of" the RecordsetClone whenever it see rs in the code.
This code:
Dim rs as Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[YourTable].[Tech#] = " & str(Nz(Me![cbxFindRecord], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Could be written:
Me.RecordsetClone.FindFirst "[YourTable].[Tech#] = " &
str(Nz(Me![cbxFindRecord], 0))
If Not Me.RecordsetClone.EOF Then Me.Bookmark = Me.RecordsetClone.Bookmark
In some ways declaring a variable is a convenience, at others it stores a
value in memory so it is not necessary to perform a complicated calculation
over and over, and at other times it may be the only practical way to
accomplish something. Loops such as looking at the Bookmark property above
are an example of the latter.
cbxFindRecord represents a naming convention, which tends to make coding
simpler than using default names such as Combo23. I use the prefixes cbo,
txt, lbl for combo boxes, text boxes, and labels. There are many other
controls and potential prefixes. The point is to come up with a system so
that you and others can better understand the code, especially when you look
at it later.
This is just scratching the surface of a complex topic. I hope I have
clarified to some extent, but my understanding is not complete enough that I
should venture further (if I have not ventured too far already). Look up
terms such as RecordsetClone, Bookmark, etc. in VBA Help, but don't count on
a clear explanation every time.
This tutorial may help with general concepts and terminology:
http://allenbrowne.com/casu-22.html
This page contains other helpful information:
http://allenbrowne.com/tips.html
There is a Links link on the right side of the page, with many more
resources.
And of course, there are the newsgroups when your research and other
learning efforts don't get you as far as you need to go.
OverMyHead said:
Thank you both very much. I cant begin to tell you all how much I
appriciate
it.
I am the kind of person that likes to know HOW you did that. How did you
know what the expression should say.
For example, I have no clue what "Dim rs ASs Object" means. Or "Set rs =
Me.Recordset.Clone" means. Knowing what it means and how to determine when
to
use it will help me write my own VB script in the future.
Also, why did you name the combo box cbxFindRecord. Im sure there is a
method to the madness there. Just curious what it is.
Thank you again to both of you.
Damon Heron said:
Here is one way to do it.
First, have the form bound to the table with your data in it.
You do this with the property sheet, Data tab, record source.
Then add all the textboxes to the form, with each bound to the table's
fields.
Next, add a combobox (the form header is a nice place for it). Make the
control source equal to the table, with the Tech# as the visible column
in
the combo. Then in the afterupdate event, do this(I'm calling the combo
cbxFindRecord):
Private Sub cbxFindRecord_AfterUpdate()
' Find the record that matches the control.
On Error GoTo Err_cbxfindRecord_AfterUpdate
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[YourTable].[Tech#] = " & str(Nz(Me![cbxFindRecord],
0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Exit_cbxFindRecord_AfterUpdate:
Exit Sub
Err_cbxfindRecord_AfterUpdate:
MsgBox Err.Description
Resume Exit_cbxFindRecord_AfterUpdate
End Sub
You may have to experiment with the combobox set up using columnwidths,
number of columns, bound column, to get it right. I can't see your table,
so
I don't know it Tech# is the PK of the table or you have an ID that is
the
primary key.
Damon
OverMyHead said:
I know how to do the basics when it comes to Access (importing data into
tables, BASIC queries, etc). However I am new to forma and can't figure
out
how to do something that I would think is remedial (and feel a bit
ignorant).
I need to create a form that will allow me to input information into a
specific field (either from a drop down or just input via a text box)
and
then populate the other fields based on the selection.
For example: I have a dbase that has fields including "Tech #", "Last
Name",
"First Name", "Phone", and "Skill Code" to name a few.
I would like to be able to select a Tech# using a drop down (or just
inputing it) and have the form auto populate the remaining fields so
that
I
can delete or edit the record.
How in the world do I do that?
