Save values displayed in unbound text fields from combo box to tab

[Sharon]

I have a form called “daily patient info†which has bound list box controls
and text boxes.

I also have an unbound combo box named combo box 111
This box was created with the combo box wizard query and is based on another
Table called Referring Providers.

The combo box displays five columns. The first column is the auto id and
primary key and is not displayed by design. When a specific row is clicked
and selected from the combo box, the combo box is designed to only display
the second column of the combo box. (This is the Referring Provider Last Name
from the Table Referring Providers)

After the combo box there are 4 unbound text fields which display the
The next four columns in the combo box (Referring Provider First Name,
Clinic, Address 1 and Specialty One) these unbound text fields display based
on combo box name and column name

I would like to be able to save the information displayed in the combo box
field and in the subsequent text fields along with the rest of the record
data entered in the Form: Daily Patient Info to Table: Daily Patient Info.

I am a beginner in terms of anything beyond basic access operations so would
love an example and happy to provide additional information. I have attempted
trial an error solutions in after update, on close, etc from previous posts
but have not been successful in finding a working solution and have failed in
trying to use the after update examples in the sample Northwind database and
similar visual basic examples in the newsgroups posting.

This is a form to be used by beginners to enter and display data regarding
each patient visit and the provider referring them and is a substitute from
repetitive direct data entry into an excel spread sheet.

 

[Olduke]

I have a form called “daily patient info†which has bound list box controls
and text boxes.

I also have an unbound combo box named combo box 111
This box was created with the combo box wizard query and is based on another
Table called Referring Providers.

The combo box displays five columns. The first column is the auto id and
primary key and is not displayed by design. When a specific row is clicked
and selected from the combo box, the combo box is designed to only display
the second column of the combo box. (This is the Referring Provider Last Name
from the Table Referring Providers)

After the combo box there are 4 unbound text fields which display the
The next four columns in the combo box (Referring Provider First Name,
Clinic, Address 1 and Specialty One) these unbound text fields display based
on combo box name and column name

I would like to be able to save the information displayed in the combo box
field and in the subsequent text fields along with the rest of the record
data entered in the Form: Daily Patient Info to Table: Daily Patient Info.

I am a beginner in terms of anything beyond basic access operations so would
love an example and happy to provide additional information. I have attempted
trial an error solutions in after update, on close, etc from previous posts
but have not been successful in finding a working solution and have failed in
trying to use the after update examples in the sample Northwind database and
similar visual basic examples in the newsgroups posting.

This is a form to be used by beginners to enter and display data regarding
each patient visit and the provider referring them and is a substitute from
repetitive direct data entry into an excel spread sheet.

With the form in design view, right click the combo/list box and select
Build Event, then Code Builder.

Select BeforeUpdate from the top right corner.

Enter the following code between the 2 existing lines.

Private Sub Combo55_BeforeUpdate(Cancel As Integer)

[fieldname] = ([Combo55],#)

End Sub

The [fieldname] is the name of the field you want to copy the data to.
Combo55 is the number of the combo box you're using.
The # refers to the column in the combo box. The columns start with number 0
(zero) in the left most column and increases by 1 for each column you move to
the right. The third column from the left would therefore be column 2.

If you wished to enter the data from the fourth column from the left in the
combo box into a field on your form named CustID you would enter:

[CustID] = ([Combo55],3)


Each column you wish to copy when you select a record in the combo box needs
its own line of code.

If you created the combo/list box using the wizard, you had the option of
saving one of the columns into a field at that time. If you did this, you
don't have to use the code to save that column, it’s already done.

 

[Al Campagna]

Sharon,
You shouldn't be saving those ancillary fields. Since you've captured
the ProviderID, you can always re-relate ProviderID to any of it's values
(Name, Clinic, Address, etc...)
You can however "display" those values on your form. Say your ProviderID
combo is Name = cboProviderID. Create a text control with a calculated
ControlSource of... (combo columns left to right are numbered 0, 1, 2, 3,
etc...)
=cboProviderID.Column(2)
would display the Clinic info.
=cboProviderID.Column(3)
would display the Address

As a general rule, never save a value that can be recreated or relinked
"on the fly" in any subsequent query, form, report...
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Sharon]

Hello and thanks for the suggestion! It appears I still have an error9s) in
my code
The error I get now is runtime error 2465: MS Office Access can't find the
field "l" referred to in your expression. When I run debug it goes to the
second line of the new combo box Before Update section. Do you see my
mistakes? - Here is the code I have for my form:

Option Compare Database ' Use database order for string comparisons.
Option Explicit ' Requires variables to be declared before they are used.
Private Sub ProviderLastName_BeforeUpdate(Cancel As Integer)
' Display message if Combo111 is blank.

Dim strMsg As String, strTitle As String
Dim intStyle As Integer

If IsNull(Me!ProviderLastname) Or Me!ProviderLastname = "" Then
strMsg = "You must pick a value from the ProviderLastName list."
strTitle = "ProviderLastName Required"
intStyle = vbOKOnly
MsgBox strMsg, intStyle, strTitle
Cancel = True
End If

End Sub

Private Sub Combo111_AfterUpdate()

End Sub

Private Sub Combo111_BeforeUpdate(Cancel As Integer)
[ProviderFirstName] = ([Combo111,2])
[ClinicName] = ([Combo111,3])
[Address One] = ([Combo111,4])
[Specialty One] = ([Combo111,5])

End Sub

Private Sub Option97_Click()

End Sub

Private Sub AddNewDoc_Click()

End Sub

Private Sub Provider_First_Name_Exit(Cancel As Integer)

End Sub

Private Sub Text109_AfterUpdate()

End Sub

I have a form called “daily patient info†which has bound list box controls
and text boxes.

I also have an unbound combo box named combo box 111
This box was created with the combo box wizard query and is based on another
Table called Referring Providers.

The combo box displays five columns. The first column is the auto id and
primary key and is not displayed by design. When a specific row is clicked
and selected from the combo box, the combo box is designed to only display
the second column of the combo box. (This is the Referring Provider Last Name
from the Table Referring Providers)

After the combo box there are 4 unbound text fields which display the
The next four columns in the combo box (Referring Provider First Name,
Clinic, Address 1 and Specialty One) these unbound text fields display based
on combo box name and column name

I would like to be able to save the information displayed in the combo box
field and in the subsequent text fields along with the rest of the record
data entered in the Form: Daily Patient Info to Table: Daily Patient Info.

I am a beginner in terms of anything beyond basic access operations so would
love an example and happy to provide additional information. I have attempted
trial an error solutions in after update, on close, etc from previous posts
but have not been successful in finding a working solution and have failed in
trying to use the after update examples in the sample Northwind database and
similar visual basic examples in the newsgroups posting.

This is a form to be used by beginners to enter and display data regarding
each patient visit and the provider referring them and is a substitute from
repetitive direct data entry into an excel spread sheet.

With the form in design view, right click the combo/list box and select
Build Event, then Code Builder.

Select BeforeUpdate from the top right corner.

Enter the following code between the 2 existing lines.

Private Sub Combo55_BeforeUpdate(Cancel As Integer)

[fieldname] = ([Combo55],#)

End Sub

The [fieldname] is the name of the field you want to copy the data to.
Combo55 is the number of the combo box you're using.
The # refers to the column in the combo box. The columns start with number 0
(zero) in the left most column and increases by 1 for each column you move to
the right. The third column from the left would therefore be column 2.

If you wished to enter the data from the fourth column from the left in the
combo box into a field on your form named CustID you would enter:

[CustID] = ([Combo55],3)


Each column you wish to copy when you select a record in the combo box needs
its own line of code.

If you created the combo/list box using the wizard, you had the option of
saving one of the columns into a field at that time. If you did this, you
don't have to use the code to save that column, it’s already done.