Solved

Using the DBCombo control with DAO

Posted on 1998-10-06
9
205 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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…

691 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