Solved

Using the DBCombo control with DAO

Posted on 1998-10-06
9
198 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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

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 200 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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

772 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