Solved

Using the DBCombo control with DAO

Posted on 1998-10-06
9
189 Views
Last Modified: 2013-12-25
Background Info:

I have two tables in an Access 97 database, Employee and State_Postcodes. The Employee table has a field called Suburb-Town which is where the Employee lives.

On a data entry form I have a DBCombo that reads in all the valid suburbs/towns from the State_Postcodes tables which updates the field Suburb-Town in Employee.

I have two data controls, one for navigating through Employee and the other, a hidden one for State_Postcodes (unsure if this is correct).

I have two 'unbound' text boxes called txtPostcode and txtState respectively.

I am using Visual Basic 5.0 Enterprise Edition

Question: When the user selects a Suburb/Town from the combo box, I want the corresponding values from the State_Postcodes table to be automatically pasted into the correct textboxes *AND* the Suburb-Town field in the Employee table to be updated appropriately. How?
0
Comment
Question by:xavierda
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 5

Expert Comment

by:chewhoung
Comment Utility
Set the datasource and datafield for the combo box to the Employee table's datasource and field.

On the change event of the combo box:

With the datacontrolforcombo.recordset
    .FindFirst "Suburb-Town = '" & cboSuburbTown.Value & "'"
    if .NoMatch = False    'found
         txtPostCode = !PostCode
         txtState = !State
    endif
End with
0
 

Author Comment

by:xavierda
Comment Utility
Thank you very much for the quick response. However, there is still a slight problem.

The solution you provided does correctly change the text boxes, both when I select a new suburb-town from the combo box and when I navigate through the employee table. (I had achieved a similar effect using SQL statements.)

But if I change the value using the combo box for some employee 'X', the correct values are entered into the text boxes, BUT the suburb-town field in employee does not reflect the change.

To clarify: Settings for DBCombo Control
BoundColumn: suburb-town
DataField: suburb-town
DataSource: dataEmployee (data control)
ListField: suburb-town
RowSource: dataTownsSuburbs (data control for the TownsAndPostcodes table)

Code in the change event:
With dataTownsSuburbs.Recordset
          .FindFirst ("[suburb-town] = '" & dbcboSuburbTown.Text & "'")
          If .NoMatch = False Then
                txtPostcode.Text = !Postcode
                txtState = !State
          End If
End With

Essentially, I have my original problem (though now more elegantly coded! :)

0
 
LVL 3

Expert Comment

by:TheAnswerMan
Comment Utility
in your
sub Combo1_Click
  Data1.recordset.findfirst "town = '" & combo1.text & "'"
end sub
0
 

Author Comment

by:xavierda
Comment Utility
Thank you for the response.

The proposed solution simply takes me to the first employee whose suburb-town field matches the value in the combo box. Chewhoung's answer correctly changes the values in the text boxes (which are read in from another table) BUT the suburb-town field is not updated in the employee table.

Here is what I want to do:
tableEmployee = name + age + address + suburb-town + etc etc
tableTownsAndPostcodes = suburb-town + state + postcode

My data aware combo box reads in the valid values from the second table. The selection is to be reflected in the tableEmployee.suburb-town field. Based on the selection the corresponding postcode and state values are pasted into two text boxes (from the tableTownsAndPostcodes field).

So far I can either get the correct values in the text boxes when I make a selection and NOT have the suburb-town field updated in the employee table OR I can get the suburb-town field to be updated BUT the text box values do not change correctly.

Ho do I get the best of both worlds?

0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 5

Expert Comment

by:chewhoung
Comment Utility
Do you mind to mail to chewhoung@yahoo.com for me to take a look?
0
 

Author Comment

by:xavierda
Comment Utility
Thank you again. In the mean time if anyone else would like to take a stab, please feel free to do so....


0
 
LVL 3

Expert Comment

by:TheAnswerMan
Comment Utility
send me project to NutsacJim@aol.com
0
 
LVL 9

Accepted Solution

by:
Dalin earned 200 total points
Comment Utility
xavierda,

I guess you want the employee table to update your new sselection from the dacombo, whose list is populated from the other table.
you need to do this:

in the click event of the DBCombo, add:

'
with datEmployeee.Recordset
        .Edit
        !suburb-town = dbComboSuburb-town.Text  
       ' if other filed need to be changed, do so
       .Update
     End With

You need to consider that a DBCombo can take entries. So you need to handle that the user entered a town not in your list. (if you covered all towns, disallowed key entry.

Regards
Dalin    
0
 

Author Comment

by:xavierda
Comment Utility
Yes, that does work. You have met the requirements of my question, the field in the Employee table does now update. (That's why I accepted your answer).

However it does introduce problems when I invoke the data control's AddNew method (If I click on the combo box now the program says it can't accept a zero length string). I could program around that, but as they say, time is of the essence.

I have found my own solution to the problem. It is by no means elegant, quite inefficient and still has its glitches but it works well enough. As time is running out for me to complete this project it will have to do!!! :)

Thank you to all who have helped me. I will be visiting the Expert's Exchange again (perhaps frequently?)....

Xavier D.a
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now