By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,894 Members | 2,193 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,894 IT Pros & Developers. It's quick & easy.

Combobox Selection not updating form bound fields

P: 22
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
Jul 21 '17 #1

✓ answered by PhilOfWalton

Whoops, sorry, posted in error

Modify the subroutine to this
Expand|Select|Wrap|Line Numbers
  1. Private Sub cbTag_AfterUpdate
  2.  
  3.     Dim Fltr as String
  4.     Fltr = "ValveID = " & cbTag
  5.     Me.Filter = Fltr
  6.     Me.FilterOn = True
  7.  
  8. End Sub
  9.  
There will be other problems but one thing at a time

Phil

Share this Question
Share on Google+
10 Replies


PhilOfWalton
Expert 100+
P: 1,430
'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
Jul 21 '17 #2

P: 22
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!
Jul 21 '17 #3

PhilOfWalton
Expert 100+
P: 1,430
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
Expand|Select|Wrap|Line Numbers
  1. Private Sub cbTag_AfterUpdate
  2.  
  3. End Sub
  4.  
Scroll to the top of the code page and ensure that it says

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
Go back to where it starts with Private Sub cbTag_AfterUpdate

Make the code read as follows

Expand|Select|Wrap|Line Numbers
  1. Private Sub cbTag_AfterUpdate
  2.  
  3.     Me.ValveID.SetFocus      ' Moves the cursor to the ValveID Control
  4.     DoCmd.FindRecord cbTag
  5.  
  6. End Sub
  7.  
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
Jul 21 '17 #4

P: 22
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
Expand|Select|Wrap|Line Numbers
  1. 'Private Sub Form_BeforeUpdate(Cancel As Integer)
  2.     'If Not BotaoGravar Then
  3.         '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
  4.     'End If
  5. '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.
Jul 21 '17 #5

PhilOfWalton
Expert 100+
P: 1,430
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

Expand|Select|Wrap|Line Numbers
  1. Private Sub cbTag_AfterUpdate
  2.  
  3.  
  4.     Me.ValveID.SetFocus      ' Moves the cursor to the ValveID Control
  5.     DoCmd.FindRecord cbTag
  6.  
  7. End Sub
  8.  
Jul 21 '17 #6

PhilOfWalton
Expert 100+
P: 1,430
Whoops, sorry, posted in error

Modify the subroutine to this
Expand|Select|Wrap|Line Numbers
  1. Private Sub cbTag_AfterUpdate
  2.  
  3.     Dim Fltr as String
  4.     Fltr = "ValveID = " & cbTag
  5.     Me.Filter = Fltr
  6.     Me.FilterOn = True
  7.  
  8. End Sub
  9.  
There will be other problems but one thing at a time

Phil
Jul 21 '17 #7

P: 22
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!
Jul 21 '17 #8

PhilOfWalton
Expert 100+
P: 1,430
Come back if you get stuck

Phil
Jul 21 '17 #9

P: 22
PhilOfWalton,

I have one more question. Should I create a new topic?
Jul 22 '17 #10

PhilOfWalton
Expert 100+
P: 1,430
I think that would be best.
Jul 22 '17 #11

Post your reply

Sign in to post your reply or Sign up for a free account.