Two textboxes connected to the same table

[ma09fraga]

Hello!

I am having a minor problem that I hope you can help me with.

I am no expert at using Access, but I usually manage to solve most of my, minor, problems. This time it is too tough for me though!

I am currently using Microsoft Access 2007. This is how far I have come:
I have made a table with a field, and in the design view I have specified the field’s data type to “number”.

Thereafter I have created a form, in which I have put two textboxes: textbox #1 and textbox #2. My issue is that I want both textboxes to be bound to the same field in the table I made.
I want to add some conditions to these textboxes, so that they function in this way:

- Since textbox number one always will be used by my users, I want the table to always retrieve information from textbox number 1.
- In some situations, my users will write numbers in both textbox 1 and textbox 2. If my users have written a value in both textbox number 1 and in textbox number 2, I want the form to display both values, but I want the table to only retrieve the value from textbox #2.

Is this possible? I have tried everything I can think of, but I just don’t get it.

I would be very grateful if anyone could help me! (I would really appreciate step-by-step instructions, if you are able to do that! J)

Thanks a bunch!

[RuralGuy]

Let's start off by saying then with what you are describing you cannot have both TextBoxes bound to the same field. I have never personally seen a situation where a form needed to have two controls bound to the same field before.

[ma09fraga]

It is like this;
My operators must write a value in textbox 1 at all times. It doesn’t matter if the value is inside or outside the tolerances, they must write the value they have measured in textbox 1. Usually, my table will retrieve values from this box.

BUT (and here is the issue),
if the value they have written is outside of the tolerances – the textbox will become red, since I have put in some conditional formatting. This means that my operators must write a new value into textbox 2. If there is a value in textbox 2, I want this value to be put in the table – instead of the value in textbox 1.

So what I’m trying to do is this:
- Always bring values from textbox 1 to the table
- If my operator has written a value in textbox 2, display both values in the form but only retrieve the value in textbox 2 to the table.
I have tried to write some codes in the After update event, but I just don’t seem to understand what I must write in order to get my form right.

Please, give me all ideas you may have.

[thhui]

You should not let the textbox to bind with the same field.

Just change the underlying control source of textbox2 to null.
and use the vba code to bind the result to the correct data field
after clicking the finish button (onclick event)

[ma09fraga]

Thank you for your reply!

I guess I did not really understand what you meant by using the VBA code to bind the result to the correct data field.

I have set the control source of textbox2 to null, so now it is unbound. What should I write next, and most important - where should I write it?

I really appreciate your help. Thanks a lot!

[RuralGuy]

Let me see if I have this correct. You want to record the value in TextBox2 unless it is empty in which case you want to record the value in TextBox1, right? What is the name of the field in your table where this value is recorded?

[thhui]

Use the following code as reference to bind the unbound textbox2 to the field1 of the database by vba code:-

Private Sub cmdButton_Click()
 
    Dim rst As DAO.Recordset
    recno = Me.CurrentRecord
    Set rst = Me.RecordsetClone
    rst.Move recno
 
    rst.Edit
    rst!field1 = Me.textbox2
    rst.Update
 
    rst.Close
    Set rst = Nothing
 
    Me.Requery
    DoCmd.GoToRecord acDataForm, Me.Name, acGoTo, recno
End Sub