Need help with textboxes & check box~!~ (1 Viewer)

LaRockera02

Registered User.
Local time
Today, 08:36
Joined
Oct 21, 2011
Messages
83
I have a database that I've added a form to and contains various tables. I have to add 2 textboxes (and modified a 3rd one),and also add a check box. I would like for the first textbox [WritingCode] to basically populate info on the other two, when I enter data into it.
Name of the other two [RepName] & [AgencyCode].

I would also like to add a check box that when the uses checks it it copys info from one textbox onto another.

Please help,

thanks, :)
 

Privateer

Registered User.
Local time
Today, 08:36
Joined
Aug 16, 2011
Messages
191
I think the easiest way is to have a table with the three fields [WritingCode], [RepName] & [AgencyCode] populated with the appropriate information. The Writer table should look like this:

[WriterID] [WritingCode] [RepName] [AgencyCode]

Then on your form, rather than a text box, you use a combo box (WriterCombo) with this table and all four fields as the row source. The combo box can display the WritingCode. The first text box will have the control source as =WriterCombo.column(2) for RepName, and the second text box will have =WriterCombo.column(3). Let me know if you need any help configuring the combo box because I left out a few things so the answer doesn't get lost in the details.
Privateer
 

LaRockera02

Registered User.
Local time
Today, 08:36
Joined
Oct 21, 2011
Messages
83
thanks Privateer for responding. The writing code, rep name, and the agency code are on the same same table (RepList). I designated the writing code as the primary key due to it being unique.

I def need your help in implementings this.

thanks
 

LaRockera02

Registered User.
Local time
Today, 08:36
Joined
Oct 21, 2011
Messages
83
I forgot to mention the RepName is divided into two fields (FirstName & LastName), I already did a query to unite both.
 

LaRockera02

Registered User.
Local time
Today, 08:36
Joined
Oct 21, 2011
Messages
83
Hey I know you are working. I just wanted to let you know I understood the instructions you gave me and it works!!! The problem I'm having know is that I also want the user to have the option of inputing the Rep Name if they don't know the writing code, then after it should populate the Writing Code and the Agency Code
 

Privateer

Registered User.
Local time
Today, 08:36
Joined
Aug 16, 2011
Messages
191
OK, then you need to change a text box to a combo box and then synchronize the two so they are on the same record. The first text box will be the rep name in the first column (and you can concatenate FN & LN here) , the second box will have the writer code in the first column, then you need to change the code a little.

In the after_update event of both combo boxes, you assign values to the other two. But there is a trick. The two combo boxes work off the primary key, the text box works off the column number. So try this:

RepNameCombo After_Update
me.writercodecombo.value = me.repnamecombo.value 'Primary key values
me.agencycode.value = me.repnamecombo.column(3) 'the column number

WriterCodeCombo After_Update
me.repnamecombo.value = me.writercodecombo.value
me.agencycode.value = me.writercodecombo.column(3)

I hope you have changed your table to have an autonumber as the primary key, it just makes life easier as you get more involved in database building.
Privateer
 

LaRockera02

Registered User.
Local time
Today, 08:36
Joined
Oct 21, 2011
Messages
83
Ok I changed the the primary key to autonumber.

I will do what you ask but I'm confused. You want me to change all 3 into combo boxes or do you have textboxes as well
OK, then you need to change a text box to a combo box and then synchronize the two so they are on the same record. The first text box will be the rep name in the first column (and you can concatenate FN & LN here) , the second box will have the writer code in the first column, then you need to change the code a little.
 

Privateer

Registered User.
Local time
Today, 08:36
Joined
Aug 16, 2011
Messages
191
If I read you correctly, your goal is to display three fields of information. So two combo boxes and one text box. Combo1=repname lookup and display, Combo2 = writercode lookup and display and textbox1 to display the agency code. Right?
 

LaRockera02

Registered User.
Local time
Today, 08:36
Joined
Oct 21, 2011
Messages
83
Correct. I would prefer that the writing code went 1st since this would be first in the tab order.

i tried what you gave me and is giving me an error "You can't assign a value to this object".
 

Privateer

Registered User.
Local time
Today, 08:36
Joined
Aug 16, 2011
Messages
191
Yeah, sorry, the combos can't have a control source, just clear out the reference to the table in both combos and the text box and lock the text box, you don't want anyone typing stuff in there. In your original scenario you needed to store the combo value in the table, which you do by binding the combo to the table. Now with two, they are synchronized to each other and not the table. So you need another place to store the primary key. I usually create another text box (RepCode) and add a line to the afterupdate of both combos.

me.RepCode.value = me.repnamecombo.value

A few things though. Because the combos are not bound to the table, when you open the form, the repcode box will have the value and the combos will be empty. So you need code to transfer the RepID from the repcode box to the two combos and the AgencyCode from one of the combos to the text box. Also, no one needs to see the repcode box, so after you have tested this a little, set the box visiblity to false.
 

vbaInet

AWF VIP
Local time
Today, 12:36
Joined
Jan 22, 2010
Messages
26,374
By the way, is the form a Single Form, Continuous Form or in Datasheet view?
 

Users who are viewing this thread

Top Bottom