Populating a text that has a control source 1

[lyonmt]

Hello and thank you for any help in advance.

I am a casual user of Access 2000.

I need to populate a text box from a combobox, but here's the catch:

The text box is bound.

I know how to update an unbound text box from a combobox by increasing the column count (in the combobox) and then putting =[Combobox].Column(1) in the control source (of the textbox).

I need to update the corresponding fields in the table though.

How is this accomplished without manipulating code? I don't know VB or SQL.

 

[clem]

I believe all you need to do is create a Combo Box on the bound form via the Combo Box Wizard. Go through the steps:

1. I want Combo Box to look up values in a table/query.

2. Select the Table/Query you are using for your Combo Box data.

3. List the Combo Box fields you will want to see on the form. Next, adjust your columns.

4. Next, Choose the field(column) from the Combo Box that you would like to use as the field to store in the table.

5. Next, Select "store that value in this field" and then choose the appropriate field from a dropdown list of fields from the table that is bound to the form.

Then when you choose the field(column) in the Combo Box, it will be placed in the designated field in your table. Hope this is what you meant.

 

[lyonmt]

I'll give it a try. Thanks

 

[lyonmt]

That doesn't exactly do what I need it to do. That seems to pull information from another table and fill in that one field.

What I need to do is that plus fill in other fields as well. I want only one drop-down box. and when a value is selected in that combo box it populates the second and third text boxes.

i.e.
Select name from the drop down and it automatically fills in the address, state and zip fields. pulling address, state and zip from the second table and filling in the current table.

I hope this makes it a little more clear.

 

[clem]

It's a little more clear, but why would you want to put identical information in two different tables (address, state and zip fields)? This seems to be redundant, doesn't it? Couldn't you join the two tables with a common field (Primary Key) to do your reporting? Can you tell me what you are trying to achieve with this form? Sorry I'm so "thick" about this.
If anyone else has any suggestions, please help.

 

[lyonmt]

Firstly, thanks for the help.

I am working on a database for a shipping company. They have four tables: Shipping log, Customers, Truckers and Shippers/Consignees. The Shipping log keeps track of day to day activity, the others are self explanatory. They want to pull a shipping company from the Shippers/Consignees table and store it in the shipping log table. This way any time during the week/year/month they want to look up whats going where during a particular week or day or month. The name field is redundant for obvious reasons, but the address and state are redundant because they are also the source or destination of a shipment, the fourth field isn't the zip, but the telephone number, so that they don't have to go search for somebody's number.

I guess that I could store all of this info in the shipping log table, but it just doesn't seem right.

I haven't set a primary key, because There isn't anything that would work well. They have a shipment number, but it can have multiple entries, because the truck can have multiple deliveries/stops/shippers/etc.

 

[clem]

I'm not sure this is the BEST way to do this (I still think a Primary Key in the Shipper/Consignee Table used as a Foreign Key in the Log Table would work), but not knowing ALL the details, I believe this will work for you as you are set up now.

Create a form using the Log table as its control source with an unbound Combo Box (Let the Wizard do the ComboBox with you). Use the fields from the Shipper's table (Name, Address, City, Phone). Put these same fields as "separate items" on this same form. Now your form has a Combo Box and then the 4 other fields. Code your Combo Box's After Update Event with the following: (all you have to do is press the event tab in the properties and click After Update and then Code Builder. Type in between the Private Sub line and the End Sub line the following code:

Private Sub ComboBoxName_AfterUpdate()

Me![FirstFieldName] = Me.ComboBoxName.Column(0)
Me![SecondFieldName] = Me.ComboBoxName.Column(1)
Me![ThirdFieldName] = Me.ComboBoxName.Column(2)
Me![FourthFieldName] = Me.ComboBoxName.Column(3)

End Sub

Designate the 4 fields' control sources to the appropriate fields in the Log table.

Add the other fields to the form that you also want to be in the Log table.

Have the ComboBox on the form be the first control stop - select your criteria, and as soon as you press Tab, the 4 fields will fill in automatically. - Then go to the next control concerning the daily activity and begin entering the rest of your data. Tab through all the controls and when you reach the end, you will be able to begin entering a new record and the previous data will now be in your Log Table.

I hope I haven't left anything out. Let me know how it goes! Good luck!

Carol L. Miller

 

[lyonmt]

Thanks, i'll try it tonight.

 

[lyonmt]

I appreciate your help very much, but it didn't work work.
The first combo box lists all the fields(information) that I need, but they do not update the other fields.
What have I done wrong?

I also tried changing the field names to field names that I am using:

Private Sub ComboBoxName_AfterUpdate()

Me![Shipper1] = Me.ComboBoxName.Column(0)
Me![Shipper1 Location] = Me.ComboBoxName.Column(1)
Me![Shipper1 State] = Me.ComboBoxName.Column(2)
Me![SHIPPER1 TEL] = Me.ComboBoxName.Column(3)
End Sub

 

[lyonmt]

And I have tried this:

Private Sub ComboBoxName_AfterUpdate()

Me![Shipper1 Location] = Me.Shipper1.Column(1)
Me![Shipper1 State] = Me.Shipper1.Column(2)
Me![SHIPPER1 TEL] = Me.Shipper1.Column(3)
End Sub

But it didn't work either...

AAARRGGGHHH!!!

 

[clem]

I created dummy tables and a form according to my instructions and it works perfectly! May I ask you the name of your ComboBox, and while you're at it, the names of the 4 fields? You will find the names in the properties under Other and Name.

I'm thinking that possibly in your code you used "ComboBoxName" literally and not the actual name of your ComboBox. Just guessing!! Don't give up. . . we'll get it!

Carol L. Miller

 

[lyonmt]

Combo boxname is: Shipper1
the text boxes are: Shipper1 Location, Shipper1 State and SHIPPER1 TEL




For "ComboBoxName" I used "Shipper1", which is the name of the combobox that the data is being pulled from.

I thought of another way to explain it:

Hopefully this will help,

I can create a combo box and have it show multiple fields. I can make unbound text boxes that display the 2nd, 3rd, etc. fields from the combo box, but I can't get the text boxes to update the appropriate fields in my table.

 

[clem]

OK! - doing great so far. . . Now does your "Form" have the "destination table" as its control source, and do your fields in the form have the appropriate destination table field names as their "Contorl Source?"

 

[lyonmt]

Yes
Yes

 

[lyonmt]

Here is the code (I think)

Option Compare Database

Private Sub Combo98_AfterUpdate()

End Sub

Private Sub Combo98_BeforeUpdate(Cancel As Integer)

End Sub

Private Sub Next_Click()
On Error GoTo Err_Next_Click


DoCmd.GoToRecord , , acNext

Exit_Next_Click:
Exit Sub

Err_Next_Click:
MsgBox Err.Description
Resume Exit_Next_Click

End Sub
Private Sub Find_Click()
On Error GoTo Err_Find_Click


Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_Find_Click:
Exit Sub

Err_Find_Click:
MsgBox Err.Description
Resume Exit_Find_Click

End Sub
Private Sub Previous_Click()
On Error GoTo Err_Previous_Click


DoCmd.GoToRecord , , acPrevious

Exit_Previous_Click:
Exit Sub

Err_Previous_Click:
MsgBox Err.Description
Resume Exit_Previous_Click

End Sub
Private Sub Add_Click()
On Error GoTo Err_Add_Click


DoCmd.GoToRecord , , acNewRec

Exit_Add_Click:
Exit Sub

Err_Add_Click:
MsgBox Err.Description
Resume Exit_Add_Click

End Sub


Private Sub ComboBoxName_AfterUpdate()

Me![Shipper1 Location] = Me.Shipper1.Column(1)
Me![Shipper1 State] = Me.Shipper1.Column(2)
Me![SHIPPER1 TEL] = Me.Shipper1.Column(3)
End Sub

Private Sub Shipper1_AfterUpdate()

End Sub

Private Sub Shipper1_BeforeUpdate(Cancel As Integer)

End Sub

Private Sub Shipper1_Location_Enter()

End Sub

 

[lyonmt]

Can I call, AIM or e-mail with you?

 

[clem]

Sorry to be so "unknowing" but what does AIM mean? Sure, you can E-mail me. You might be able to AIM me too, once I know what it means. :>) My e-mail is clmiller1@home.com.

Would you be able to zip your database and e-mail it to me or something similar?

 

[clem]

Your textboxes in your form should be "bound textboxes".

They become bound when you specify in each of their control properties on the form, their "Control Source", which is the field name in the "destination table" The table field names should drop down when you click on the control source property for each control on the form - that is if you have specified the entire form's Record Source as the same destination table.

I think this is where you are falling short of having the form's controls go to the table.

 

[lyonmt]

Actually, because they are bound I am having a problem. I can get this to work if they are unbound.

 

[lyonmt]

Thanks again for all your help! The world is a better place with people like you in it.