• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 213
  • Last Modified:

Using the DBCombo control with DAO

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?
  • 4
  • 2
  • 2
  • +1
1 Solution
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
End with
xavierdaAuthor Commented:
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! :)

in your
sub Combo1_Click
  Data1.recordset.findfirst "town = '" & combo1.text & "'"
end sub
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

xavierdaAuthor Commented:
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?

Do you mind to mail to chewhoung@yahoo.com for me to take a look?
xavierdaAuthor Commented:
Thank you again. In the mean time if anyone else would like to take a stab, please feel free to do so....

send me project to NutsacJim@aol.com

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
        !suburb-town = dbComboSuburb-town.Text  
       ' if other filed need to be changed, do so
     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.

xavierdaAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now