When you want to put code in an event for a form or any controls on the form,
open the form in design view.
Select the form or the control you want to add the code to. Open the
properties dialog and select the Events tab.
Select the event you want to add code to and click the small command button
with the 3 dots. If there is already code in the event, the VB editor will
open showing the existing event code. If there is no code for the event, you
will get a dialog box. Select Code Builder. The VB editor will open, but
there will be no code in the event. All you will see is the declaration of
the event name and the line End Sub. For example, lets say you have a combo
box names cboSearch and you want to enter code for the combo's After Update
event. You would see:
Private Sub cboSearch_AfterUpdate()
End Sub
You would then enter the code for the event between these two lines.
Creating a new number and searching for an exiting number are two different
things. The combo I suggested previously would be for selecting the division
code for a new record so it would know which number to increment.
To search for an existing record, you would need another unbound combo that
would allow you to search for a combination of the fields.
Before we take that on, lets get adding a new record working.
As to the names. I don't know what your names are, but you need to change
any names in the code to match the names of your tables, controls, fields,
etc.
You may see existing code, but you need to see what event it relates to. If
there is already code in an event you want to add code to, you need to make
sure the new code doesn't interfer with the existing code.
If you are not that familiar with VBA, I would suggest you visit this site.
There are a lot of examples and tutorials that will help you out:
http://allenbrowne.com/
--
Dave Hargis, Microsoft Access MVP
bbypookins said:
I'm still confused how one would search for a record based on the combo if
it is only visible for new records. Records will need to be repeatedly
accessed after they have been entered and I need to make it quick and easy
for users to do this simply by searching for the combo number.
Also, I'm sorry, but coding is rather foreign to me. When I go to enter the
code for the unbound combo box in the After Update, the VBA window opens and
there is a bunch of code in there already...I'm lost as to where to put this
new code. And should "TableName" be the name of the table I'm storing the
info in (tblRPALog)? Is that the only code I need to replace with my
information? Whatever I'm doing, it's not working.
When you say in the Form current event, do you mean to click that little box
in the upper left corner to select the whole form and put the code in the
current event of that? Again, where, when the window opens, do I put the code.
Klatuu said:
Notice the code for the form current event. That makes the the combo visible
for new records.
Then when the combo is made visible and the user selects a division, the
number is assigned and the bound controls are populated.
As to two fields compared to one combined field. Using two fields provides
more flexibility and make coding easier. Don't confuse what is carried in
the database with what is presented visually to the user. The user will see
both values. You can position the controls so the user sees the division and
number together.
The user has to select a division before a number can be created.
--
Dave Hargis, Microsoft Access MVP
:
The user needs to be able to quickly and easily access the record based on
that combined number. How would they do the field is not visible on the form
and is not logged in the table as one combined field?
:
You will need an unbound control on your Access Form to select the division.
I would use a combo box. In your table, I would use 2 fields - one for the
Division and one for the number. In the After Update event of the combo box,
determine the current highest number for the selected division and add one
to it. Also, I would make this combo box invisible except for new records.
Private Sub cboDivisonSelect_AfterUpdate()
Me.txtDivision = Me.cboDivisionSelect
Me.txtSeqNumber = Nz(DLookup("[SeqNumber]", "TableName", "[Division] =
""" & Me.cboDivisionSelect & """"), 0) + 1
End Sub
Now in the Form Current Event:
With Me
If .NewRecord Then
.cboDivisionSelect.Visible = True
.cboDivisionSelect.SetFocus
Else
.cboDivisionSelect.Visible = False
End If
End Sub
--
Dave Hargis, Microsoft Access MVP
:
I'm talking about a hard copy form we that comes into our office. I'm
creating a database to log these forms.
:
I don't think you receive forms. Forms do not contain any data. Data is
contained in tables. Forms are used to present and manipulate data.
What are you actually receiving?
--
Dave Hargis, Microsoft Access MVP
:
I'm creating a database to track a particular form that we receive in our
office. When we receive the form we give it a unique number that combines the
division it came from and a sequential number pertaining to that division
only. In other words, when a form comes in from Executive, it will be given
the number EXE-001 and the next time one form Executive comes in it will be
EXE-002, and so on. When one from Finance & Administration comes in it will
be numbered FA-001, then FA-002, etc.
What I'd like to be able to do in the form is have a combo box field to
select the division from qryDivision and then have the sequential number be
generated automatically depending on what division you choose. Then, I'd like
those two fields to be combined in one field (RPA_No) in the main table
(tblRPALog).
I have no idea how to do this and I'm easily confused when it comes to coding.
