Auto Fill Form

[Joanne Lewis]

I am having a great deal of difficulty with a form. Basically, I would
like to enter an account # and have the account #, patient first name,
and patient last name automatically fill.

The form Record Source table is "InitialInfoFromSW". I want to enter
an account #, have it look in another table called "AS400 Imported
Data", and automatically fill in the fields called "LastName" and
"FirstName" into the form's text boxes.

It was suggested to me to use a combo box's AfterUpdate event in the
form header to open a recorset from the AS400... table. This is the
code (that I cannot get to work correctly....):

Option Compare Database
Option Explicit

Private Sub cboAccount_AfterUpdate()
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSQL As String

'Open a connection to this database file
Set cnn = InitialInfoFromSW.Connection

'Open a recordset with the selected Account
Set rs = New ADODB.Recordset
strSQL = "SELECT * FROM [AS400 Imported Data]" _
& "WHERE Account='" & Me.cboAccount & "'"
rs.Open strSQL, cnn, adOpenStatic

'Fill the textboxes with the info in the recordset.
Me.txtAccount = rs!Account
Me.txtLastName = rs!LastName
Me.txtFirstName = rs!FirstName

'Clear the objects from memory.
Set rs = Nothing
Set cnn = Nothing
End Sub
I would be extremely grateful for any help!!

Joanne

[Fred Zuckerman]

"Joanne Lewis" <jl*****@chw.edu> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

I am having a great deal of difficulty with a form. Basically, I would
like to enter an account # and have the account #, patient first name,
and patient last name automatically fill.

The form Record Source table is "InitialInfoFromSW". I want to enter
an account #, have it look in another table called "AS400 Imported
Data", and automatically fill in the fields called "LastName" and
"FirstName" into the form's text boxes.

It was suggested to me to use a combo box's AfterUpdate event in the
form header to open a recorset from the AS400... table. This is the
code (that I cannot get to work correctly....):

Option Compare Database
Option Explicit

Private Sub cboAccount_AfterUpdate()
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSQL As String

'Open a connection to this database file
Set cnn = InitialInfoFromSW.Connection

'Open a recordset with the selected Account
Set rs = New ADODB.Recordset
strSQL = "SELECT * FROM [AS400 Imported Data]" _
& "WHERE Account='" & Me.cboAccount & "'"
rs.Open strSQL, cnn, adOpenStatic

'Fill the textboxes with the info in the recordset.
Me.txtAccount = rs!Account
Me.txtLastName = rs!LastName
Me.txtFirstName = rs!FirstName

'Clear the objects from memory.
Set rs = Nothing
Set cnn = Nothing
End Sub

I would be extremely grateful for any help!!
Joanne

----------------------------------------------------------------

Joanne,
I think you have over-complicated the process.
If your combobox, cboAccount, has the following 3 columns:
Account
Lastname
Firstname

Then your only code needs to be:

Private Sub cboAccount_AfterUpdate()
Me.txtLastname = Me.cboAccount.Column(1)
Me.txtFirstname = Me.cboAccount.Column(2)
End Sub

Also, you don't need the control named txtAccount. Just use the control
named cboAccount.

I Hope That Helps,
Fred Zuckerman

[Fred Zuckerman]

"Fred Zuckerman" <Zu********@sbcglobal.net> wrote in message
news:dF****************@newssvr14.news.prodigy.com ...

"Joanne Lewis" <jl*****@chw.edu> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

I am having a great deal of difficulty with a form. Basically, I would
like to enter an account # and have the account #, patient first name,
and patient last name automatically fill.

The form Record Source table is "InitialInfoFromSW". I want to enter
an account #, have it look in another table called "AS400 Imported
Data", and automatically fill in the fields called "LastName" and
"FirstName" into the form's text boxes.

It was suggested to me to use a combo box's AfterUpdate event in the
form header to open a recorset from the AS400... table. This is the
code (that I cannot get to work correctly....):

Option Compare Database
Option Explicit

Private Sub cboAccount_AfterUpdate()
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSQL As String

'Open a connection to this database file
Set cnn = InitialInfoFromSW.Connection

'Open a recordset with the selected Account
Set rs = New ADODB.Recordset
strSQL = "SELECT * FROM [AS400 Imported Data]" _
& "WHERE Account='" & Me.cboAccount & "'"
rs.Open strSQL, cnn, adOpenStatic

'Fill the textboxes with the info in the recordset.
Me.txtAccount = rs!Account
Me.txtLastName = rs!LastName
Me.txtFirstName = rs!FirstName

'Clear the objects from memory.
Set rs = Nothing
Set cnn = Nothing
End Sub

I would be extremely grateful for any help!!
Joanne

----------------------------------------------------------------

Joanne,
I think you have over-complicated the process.
If your combobox, cboAccount, has the following 3 columns:
Account
Lastname
Firstname

Then your only code needs to be:

Private Sub cboAccount_AfterUpdate()
Me.txtLastname = Me.cboAccount.Column(1)
Me.txtFirstname = Me.cboAccount.Column(2)
End Sub

Also, you don't need the control named txtAccount. Just use the control
named cboAccount.

I Hope That Helps,
Fred Zuckerman

+Hi Fred,
+Thanks for your help. I tried this, but it still is not working....
+Maybe I'm doing something else wrong. The combobox in the form header
+is unbound; the row source is:
+
+SELECT DISTINCTROW [AS400 Imported Data].[ID], [AS400 Imported
+Data].[Account], [AS400 Imported Data].[LastName], [AS400 Imported
+Data].[FirstName] FROM [AS400 Imported Data];
+
+The Account, Lastname, Firstname are just text boxes in the detail
+section.
+
+Am I missing something?
+
+Thanks for your help!!!!!
+
+Joanne
+

Joanne,
Please reply to the group. Others may have the same questions and could
learn from the responses.

It looks like your combo box has the following columns:
ID
Account
Lastname
Firstname

So the code would be:

Private Sub cboAccount_AfterUpdate()
Me.txtLastname = Me.cboAccount.Column(2)
Me.txtFirstname = Me.cboAccount.Column(3)
End Sub

The combo box may need to be in the detail section, too.

Fred