Filling fields automatically

G

Guest

I am currently trying to create a form that will automatically fill in some
information once an ID has been entered. The info i want prefilled is all
stored in the same table as the ID number. I get it to work somewhat. When
I enter the ID number currently it will automatically fill in the information
from the first record and then duplicates the record. I am currently using
the code below to have the info prefilled. What am I doing wrong? I do not
want info duplicated and i want it to pull the correct corresponding records.


Sub SchoolCode_Exit(Cancel As Integer)
Dim varName, varAE, varFAO As Variant
varName = DLookup("SchoolName", "tblSchool", "SchoolCode =[SchoolCode] ")
varAE = DLookup("AEName", "tblSchool", "SchoolCode =[SchoolCode] ")
varFAO = DLookup("FAOName", "tblSchool", "SchoolCode =[SchoolCode] ")
If (Not IsNull(varName)) Then Me![SchoolName] = varName
If (Not IsNull(varAE)) Then Me![AEName] = varAE
If (Not IsNull(varFAO)) Then Me![FAOName] = varFAO
End Sub
 
G

Guest

I notice you are doing your DLookups in tblSchool. What table is your form
based on? Is SchoolCode a unique value in tblSchool or can there be more than
one record in tblSchool with the same SchoolCode?
Also, I notice you are using the Exit event. I would suggest the Before
Update event would be a better location for your code.
One other thing that will improve performance. Domain aggregate functions
like DLookup are useful in the right place, but can really slow down an
appication. A faster method would be to create a recordset using a query
that is based on tblSchool and filtered on the value in your SchoolCode
control.
Basically, just create a query and in the Criteria row for SchoolCode, put
in a reference to your control:
[forms]![myformname]![SchoolCode]

Then in your sub:

Sub SchoolCode_BeforeUpdate(Cancel As Integer)
Dim rstSchool As Recordset

Set rstSchool = CurrentDb.OpenRecordset("qselSchool")
If rstSchool.RecordCount = 0 Then
MsgBox "School Code Not Found"
Cancel = True
Else
Me![SchoolName] = rstSchool!SchoolName
Me![AEName] = rstSchool!AEName
Me![FAOName] = rstSchool!FAOName
End If
Set rstSchool = Nothing
End Sub

Just a note about dimming. Since you declared varFAO as Variant, this line
is not a problem.
Dim varName, varAE, varFAO As Variant
However, if it were
Dim strName, strAE, strFAO As String

You would not have 3 strings, you would have 2 Variants and 1 string. Each
varialble has to be cast separately. If no type is cast, then it defaults to
Variant. So to get 3 strings:
Dim strName As String, strAE As String, strFAO As String
 
G

Guest

Thank you for your fast response. I have not yet tried your suggestion but I
will be trying it soon. i just wanted to answer some of your questions. My
form is based off of my tblSchool and the School Code is unique to each
particular school so I do not want more than one record associated with that
same code.

I will try your suggestions and get back to you.
Thanks

Klatuu said:
I notice you are doing your DLookups in tblSchool. What table is your form
based on? Is SchoolCode a unique value in tblSchool or can there be more than
one record in tblSchool with the same SchoolCode?
Also, I notice you are using the Exit event. I would suggest the Before
Update event would be a better location for your code.
One other thing that will improve performance. Domain aggregate functions
like DLookup are useful in the right place, but can really slow down an
appication. A faster method would be to create a recordset using a query
that is based on tblSchool and filtered on the value in your SchoolCode
control.
Basically, just create a query and in the Criteria row for SchoolCode, put
in a reference to your control:
[forms]![myformname]![SchoolCode]

Then in your sub:

Sub SchoolCode_BeforeUpdate(Cancel As Integer)
Dim rstSchool As Recordset

Set rstSchool = CurrentDb.OpenRecordset("qselSchool")
If rstSchool.RecordCount = 0 Then
MsgBox "School Code Not Found"
Cancel = True
Else
Me![SchoolName] = rstSchool!SchoolName
Me![AEName] = rstSchool!AEName
Me![FAOName] = rstSchool!FAOName
End If
Set rstSchool = Nothing
End Sub

Just a note about dimming. Since you declared varFAO as Variant, this line
is not a problem.
Dim varName, varAE, varFAO As Variant
However, if it were
Dim strName, strAE, strFAO As String

You would not have 3 strings, you would have 2 Variants and 1 string. Each
varialble has to be cast separately. If no type is cast, then it defaults to
Variant. So to get 3 strings:
Dim strName As String, strAE As String, strFAO As String


MimiSD said:
I am currently trying to create a form that will automatically fill in some
information once an ID has been entered. The info i want prefilled is all
stored in the same table as the ID number. I get it to work somewhat. When
I enter the ID number currently it will automatically fill in the information
from the first record and then duplicates the record. I am currently using
the code below to have the info prefilled. What am I doing wrong? I do not
want info duplicated and i want it to pull the correct corresponding records.


Sub SchoolCode_Exit(Cancel As Integer)
Dim varName, varAE, varFAO As Variant
varName = DLookup("SchoolName", "tblSchool", "SchoolCode =[SchoolCode] ")
varAE = DLookup("AEName", "tblSchool", "SchoolCode =[SchoolCode] ")
varFAO = DLookup("FAOName", "tblSchool", "SchoolCode =[SchoolCode] ")
If (Not IsNull(varName)) Then Me![SchoolName] = varName
If (Not IsNull(varAE)) Then Me![AEName] = varAE
If (Not IsNull(varFAO)) Then Me![FAOName] = varFAO
End Sub
Click to expand...
Click to expand...
 
G

Guest

I used the code you provided and altered it slightly to fit my database but
it is coming back with a debug error stating "Run-time error '3061'. Too Few
parameters: Expected 1" after clicking debug it highlights the line that is
Set.rstSchool=CurrentDb.OpenRecordset("qrySchool")

Below is the code i used.

Sub SchoolCode_BeforeUpdate(Cancel As Integer)
Dim rstSchool As Recordset
Set rstSchool = CurrentDb.OpenRecordset("qrySchool")
If rstSchool.RecordCount = 0 Then
MsgBox "School Code Not Found"
Cancel = True
Else
Me![SchoolName] = rstSchool!SchoolName
Me![AEName] = rstSchool!AEName
Me![FAOName] = rstSchool!FAOName
End If
Set rstSchool = Nothing
End Sub

Klatuu said:
I notice you are doing your DLookups in tblSchool. What table is your form
based on? Is SchoolCode a unique value in tblSchool or can there be more than
one record in tblSchool with the same SchoolCode?
Also, I notice you are using the Exit event. I would suggest the Before
Update event would be a better location for your code.
One other thing that will improve performance. Domain aggregate functions
like DLookup are useful in the right place, but can really slow down an
appication. A faster method would be to create a recordset using a query
that is based on tblSchool and filtered on the value in your SchoolCode
control.
Basically, just create a query and in the Criteria row for SchoolCode, put
in a reference to your control:
[forms]![myformname]![SchoolCode]

Then in your sub:

Sub SchoolCode_BeforeUpdate(Cancel As Integer)
Dim rstSchool As Recordset

Set rstSchool = CurrentDb.OpenRecordset("qselSchool")
If rstSchool.RecordCount = 0 Then
MsgBox "School Code Not Found"
Cancel = True
Else
Me![SchoolName] = rstSchool!SchoolName
Me![AEName] = rstSchool!AEName
Me![FAOName] = rstSchool!FAOName
End If
Set rstSchool = Nothing
End Sub

Just a note about dimming. Since you declared varFAO as Variant, this line
is not a problem.
Dim varName, varAE, varFAO As Variant
However, if it were
Dim strName, strAE, strFAO As String

You would not have 3 strings, you would have 2 Variants and 1 string. Each
varialble has to be cast separately. If no type is cast, then it defaults to
Variant. So to get 3 strings:
Dim strName As String, strAE As String, strFAO As String


MimiSD said:
I am currently trying to create a form that will automatically fill in some
information once an ID has been entered. The info i want prefilled is all
stored in the same table as the ID number. I get it to work somewhat. When
I enter the ID number currently it will automatically fill in the information
from the first record and then duplicates the record. I am currently using
the code below to have the info prefilled. What am I doing wrong? I do not
want info duplicated and i want it to pull the correct corresponding records.


Sub SchoolCode_Exit(Cancel As Integer)
Dim varName, varAE, varFAO As Variant
varName = DLookup("SchoolName", "tblSchool", "SchoolCode =[SchoolCode] ")
varAE = DLookup("AEName", "tblSchool", "SchoolCode =[SchoolCode] ")
varFAO = DLookup("FAOName", "tblSchool", "SchoolCode =[SchoolCode] ")
If (Not IsNull(varName)) Then Me![SchoolName] = varName
If (Not IsNull(varAE)) Then Me![AEName] = varAE
If (Not IsNull(varFAO)) Then Me![FAOName] = varFAO
End Sub
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Subform Issues! 6
Combo Box Selection 2
On Form Close Event 2
Auto field update on combo selection 4
Access How to check that the value of a field in a new record is not present in 2 fields of the table? 0
How do I create a look up module? 1
Help with Microsoft help on linking Access to Outlook 17
How to pass multiple Fields to new Form 2

Top