Combobox Selection not updating form bound fields

[simaonobrega]

Dear Bytes community,

Framework: I have created a form using the "Form Design" tool. On the form property sheet, it was added to the Record Source the table which contained the fields that I wanted to update/add.
The fields from the table were introduced in the form using the "Add Existing Fields" button.
In the next step, in order to have the fields populated by a combobox selection, I added a combobox with the option "Find a record on my form based on the value I selected in my combobox.".

Problem: After selecting a combobox value, the fields in the form didn't update themselves.

Question: Any idea why this fields don't update?

Since Access already have the connections made internally, I think there should be a clenear way of accomplishing the goal without using Column property, DlookUp, subforms or recordset code (I thought the one implemented would be the faster way but it is not working).

Thank you very much in advance for your time and help.

Best regards

[PhilOfWalton]

'Fraid that's the problem using Wizards.

So several questions.
What is the RowSource of your Combo Box, which is the Bound Column, How many columns and how wide are they?

Can you let us have the names of all the fields on your form.

Phil

[simaonobrega]

PhilOfWalton,

Thank you for your answer. I will put here a sample of the table and form (in order to be easier to debug).
In the next image we can see the names of all fidels of my form:

Relatively to the Combobox/form, it is possible to analyse the details in the next image:

The form has Valves table as record source.

Thank you!

[PhilOfWalton]

OK, you need a bit of code to get this to work (Most of your Combo boxes to find a record will work the same way).

You also need on your form a field called ID bound to the ID in your table. Incidentally, for clarity, I would rename both the table and control ValveID (The default of Access is to name the first line of all your tables "ID", and it gets very confusing.)

Go to the Event Tab on the Property Sheet. On the After Update , if you type a square bracket [, it will change this to [Event Procedure].
Click on this line and 3 dots ... will appear at the end of the line. Click on these.

You are now writing code.
It will say at this point

Private Sub cbTag_AfterUpdate
 
End Sub
 

Scroll to the top of the code page and ensure that it says

Option Compare Database
Option Explicit
 

Go back to where it starts with Private Sub cbTag_AfterUpdate

Make the code read as follows

Private Sub cbTag_AfterUpdate
 
    Me.ValveID.SetFocus      ' Moves the cursor to the ValveID Control
    DoCmd.FindRecord cbTag
 
End Sub
 

Yet another "Incidentally", if you get a number of valves from the same manufacturer, you should have a table of manufacturers. That way their name is only entered once in the whole database, and no chance of misspelling it.

Phil

[simaonobrega]

PhilOfWalton,

Thank you for your repply and very detailed explanation. It is always good to learn new programming methods.
After implementing your routine, error 3021 (No current record) appeared.
Also, ValveID is always giving me numbers (check image)

https://www.dropbox.com/s/45ijj37o0r...r3021.png?dl=0

Well, the error is eliminated if the routine

'Private Sub Form_BeforeUpdate(Cancel As Integer)
    'If Not BotaoGravar Then
        'Cancel = True 'Se o botão gravar não tiver sido ativado, então o formulário não atualiza informação para a tabela
    'End If
'End Sub

is eliminated. I use this routine to update the Data to the table via the bound fields using a Save button.

Nevertheless, the fields are not updating. I think this is happening because the ValveID field is not being updated after the TAG selection.
Best regards.

[PhilOfWalton]

I suspect your problem is that as soon as you Load the form, you run Sub_Limpar_Click which clears everything out.

Sorry, I don't speak Portuguese so I have to translate your comments

So if you only want to see the record that you find in the cbTag Combo Box, and not be able to scroll through the records we need a different technique.
Firstly comment out the line Limpar_Click in your Sub Form_Load procedure

Then modify the Subroutine I gave you to this

Private Sub cbTag_AfterUpdate
 
 
    Me.ValveID.SetFocus      ' Moves the cursor to the ValveID Control
    DoCmd.FindRecord cbTag
 
End Sub
 

[PhilOfWalton]

Whoops, sorry, posted in error

Modify the subroutine to this

Private Sub cbTag_AfterUpdate
 
    Dim Fltr as String
    Fltr = "ValveID = " & cbTag
    Me.Filter = Fltr
    Me.FilterOn = True
 
End Sub
 

There will be other problems but one thing at a time

Phil

[simaonobrega]

PhilOfWalton,

Thank you very much. The code is working perfectly!!
You have been excelent. Now I just have to solve some incompatibilities with my code.

Amazing!! Just what I wanted!

[PhilOfWalton]

Come back if you get stuck

Phil

[simaonobrega]

PhilOfWalton,

I have one more question. Should I create a new topic?

[PhilOfWalton]

I think that would be best.