Okay, the form is good.
I have created a new combo box and in the Row Source I have put
SELECT DecalNumber FROM DecalStall
in the AfterUpdate I have made an EventProcedure with the code you provided
- hopefully putting the right tablenames in where they were supposed to go.
I did the same for the OnNotInList - I copied your code and put it in the
window.
I am getting an error message on this line
it is in red) and it points to
the Then
If Not(IsNull(DLookUp("[DecalNumber]", "[DecalStall]", "[DecalNumber] = '" & _
Me.cboDecalNumber & "'" Then
Set rst = Me.RecordsetClone
This is my whole visual basic window (for your reference)
Option Compare Database
Private Sub Combo38_AfterUpdate()
Private Sub cboDecal_AfterUpdate()
Dim rst As Recordset
If Not(IsNull(DLookUp("[DecalNumber]", "[DecalStall]", "[DecalNumber] =
'" & _
Me.cboDecalNumber & "'" Then
Set rst = Me.RecordsetClone
rst.FindFist "[DecalNumber] = '" & Me.cboDecalNumber & "'"
If Not rst.NoMatch Then
Me.Bookmark = rst.Bookmark
End If
End If
End Sub
Private Sub Combo38_NotInList(NewData As String, Response As Integer)
Dim rst As Recordset
If MsgBox(NewData & " Is Not In The Attribute Table " & vbNewLine _
& "Do you want to add it", _
vbInformation + vbYesNo, "Not Found") = vbYes Then
CurrentDb.Execute ("INSERT INTO CISAttributeTable (ACTIVITY) " _
& "VALUES ('" & NewData & "');"), dbFailOnError
Me.Requery
Set rst = Me.RecordsetClone
rst.FindFirst "[Activity] = '" & NewData & "'"
Me.Bookmark = rst.Bookmark
Set rst = Nothing
Response = acDataErrAdded
Else
Me.cboActivity.Undo
Response = acDataErrContinue
End If
End Sub
Michelle
PS- thanks for your patience
:
Not exactly.
First, open your form in design mode.
Check the Record Source property of your form. It should be the table or a
query based on the table.
Then check the Control Source property of each control that should show a
field in your table and be sure it is the field you want.
Now, if you use the navigation buttons to more through the table, you will
see all the fields for all the records in the table.
If this is working to this point, so far so good.
Now, if you type a decal number in the decal control, it will not position
you on the record for that decal, it will change whatever decal number is in
the table for that record to the decal number you type in.
These controls are for editing, not for searching. This is where we need a
combo that is not bound to any field in the table. It is used only to look
up a record and make it the current record in your form.
Here is the code again for a reminder. Once we get this working, we can
worry about adding new records.
Private Sub cboDecal_AfterUpdate()
Dim rst As Recordset
If Not(IsNull(DLookUp("[Decal]", "[TableNameHere]", "[Decal] = '" & _
Me.cboDecal & "'" Then
Set rst = Me.RecordsetClone
rst.FindFist "[Decal] = '" & Me.cboDecal & "'"
If Not rst.NoMatch Then
Me.Bookmark = rst.Bookmark
End If
End If
End Sub
Note the above code assumes that Decal is a text field in the table. If it
is a numeric field, we have to take the single qoutes out. It would be like
this:
Private Sub cboDecal_AfterUpdate()
Dim rst As Recordset
If Not(IsNull(DLookUp("[Decal]", "[TableNameHere]", "[Decal] = " & _
Me.cboDecal Then
Set rst = Me.RecordsetClone
rst.FindFist "[Decal] = " & Me.cboDecal
If Not rst.NoMatch Then
Me.Bookmark = rst.Bookmark
End If
End If
End Sub
When you create the combo box, the Row Source Property should be a select
statement that returns all the decal numbers in the table:
SELECT Decal FROM TableName
See if you can get this far, then we will move on.
Post back if you have questions about this part.
That is where we get into the code I provided for using a combo to search.
:
Thanks again - you are quick!
I believe my main issue is one you pointed out before:
As to your form question, If you are using a bound form and the decal is in
a record in the table, and all table fields are bound to a control on the
form, they will show up, so if they are not, then your form is not set up
properly.
I am going to assume my form is not set up properly - because when I type in
a decalnumber, you are saying that if the form is set up right, and the
relationship is there, then the fields should fill in automatically?
Please feel free to curse at the monitor now as I feel frustration coming on.
:
Access has no way of knowing what lot or stall to assign until you tell it.
It can't autopopulate on it's own.
As I said in my previous post, once you have entered a lot and stall for a
decal, then select that decal and you have a way to find the existing record
in the table, it will pull up the data in your form.
:
I appreciate the answer very much - it is very thorough!
Still a little lost, I am...
In my main form, I want to be able to type in DecalNumber (sorry, I did have
them set up right) and hit TAB and have the next two fields (lotname and
stallnumber) fill in.
Is there an easy way to do this? I understand I ask a lot - but I am not
familiar with all this coding you have at the bottom.
I am hoping that I can just click on the decalnumber box (in design view)
and maybe enter a control source? or a statement that pulls the data from
the decalstall table?
Honestly, I need a walk-through to do this. Pretty much all my Access
knowledge is from learning on my own - and I can be pretty slow sometimes!
Thanks again,
Michelle
:
Your question is a bit confusing. That is because until someone enters the
parking lot and stall for a decal, there is no way for Access to know which
lot or stall to assign to the decal.
To do this correctly, you need at least two tables. One for
decal/lots/stalls and one for information on the decal. I am sure you have
information about the decal that tells you who the decal was issued to, car
make, model, liscense number, etc. That will be the other table.
Your decal table is correctly layed out. The other table would be like
DecalNumber
LastName
FirstName
CarMake
CarModel
CarLisc
ExpireDate
etc.
The decal number should be the primary key for both tables.
As to your form question, If you are using a bound form and the decal is in
a record in the table, and all table fields are bound to a control on the
form, they will show up, so if they are not, then your form is not set up
properly.
The usual technique when you are wanting to search a form for a value (decal
number) in this case, is to use an unbound combo box with the record source
being something like:
SELECT DecalNumber FROM MyTableNameHere
Then you use the AfterUpdate event of the combo to look up the decal number
and it will load the record into your form if the number exists. You will
also need to use the NotInList Event of the combo. You can use it to present
a message box with something like "Decal Number Not Found, Do You Want To
Assign it A Location"
and create a new record for it.
Now the coding in those events is up to you, but here is an example from
some of my code for both events. Be aware that for the NotInList event to
fire, the Limit To List propety for the combo must be set to yes.
Private Sub cboActivity_NotInList(NewData As String, Response As Integer)
Dim rst As Recordset
If MsgBox(NewData & " Is Not In The Attribute Table " & vbNewLine _
& "Do you want to add it", _
vbInformation + vbYesNo, "Not Found") = vbYes Then
CurrentDb.Execute ("INSERT INTO CISAttributeTable (ACTIVITY) " _
& "VALUES ('" & NewData & "');"), dbFailOnError
Me.Requery
Set rst = Me.RecordsetClone
rst.FindFirst "[Activity] = '" & NewData & "'"
Me.Bookmark = rst.Bookmark
Set rst = Nothing
Response = acDataErrAdded
Else
Me.cboActivity.Undo
Response = acDataErrContinue
End If
End Sub
------------------------
Private Sub cboActivity_AfterUpdate()
Dim rst As Recordset
If Not(IsNull(DLookUp("[Activity]", "[CisAttributeTable]", "[Activity] =
'" & _
Me.cboActivity & "'" Then
Set rst = Me.RecordsetClone
rst.FindFist "[Activity] = '" & Me.cboActivity & "'"
If Not rst.NoMatch Then
Me.Bookmark = rst.Bookmark
End If
End If
End Sub
One other thing, take the spaces out of your field names. The basic rule
for naming in Acess, be it fields, controls, or any object. Use only
Letters, Numbers, and the UnderScore. Do Not use space, any special
characters (#, $, %, etc) or any Access reserved words (Month, Year, Date,
etc)
The easiest way to avoid that problem is to use standard naming conventions.
Here is a site that will help with that:
http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnaraccess/html/msdn_20naming.asp
:
Being relatively new to Access, I am trying to make a form auto fill several
fields for me. I understand this is possible yet, I haven't had any luck in
achieving it.
1) I have a table set up for parking decal number - parking lot - parking
stall These always are the same, although they are in different fields of
the table.
Field 1: Decal number
Field 2: Parking Lot
Field 3: Parking Stall
eg. decal number 1246 is then on the same line as parking lot 9511A and
stall number 00026
If in the form, I type in decal #, I would like it to take from the table
