?
Solved

Using the DBCombo control with DAO

Posted on 1998-10-06
9
Medium Priority
?
210 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
ID: 1496690
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
ID: 1496691
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
ID: 1496692
in your
sub Combo1_Click
  Data1.recordset.findfirst "town = '" & combo1.text & "'"
end sub
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:xavierda
ID: 1496693
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
 
LVL 5

Expert Comment

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

Author Comment

by:xavierda
ID: 1496695
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
ID: 1496696
send me project to NutsacJim@aol.com
0
 
LVL 9

Accepted Solution

by:
Dalin earned 600 total points
ID: 1496697
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
ID: 1496698
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month17 days, 2 hours left to enroll

862 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