Results 1 to 10 of 10
  1. #1
    Join Date
    Jun 2010
    Posts
    186

    Unanswered: Combo box trouble - what have I done???

    I have a form frmAddAccount to enter accounts, name address phone - all that.
    It comes from table tblAccountNames of the same, of 4,500 records that were imported from exel.
    I have a few problems -
    1st.....my zip codes are leading zeros, but they did not import to the table from exel that way. I'm getting 4 digits not 5. Data type is text.
    2nd....for entry purposes, there is a look up of zip codes in my region (tblTownZip) . Field is data type number, and format is set to 00000, so I'm getting the leading zeros
    3rd......Form frmAddAccount has the zip field changed to combo box, where I can select the zip code from a list....the list shows me zip, city and state. The OnClick here populates all 3 fields; but I would only get 4 digit zip codes.

    In trying to correct this - I went back to the tblAccountNames and changed ZIP (text) to a look-up, and they changed to a five-digit zip code, although not the correct one. Somehow the correct one is showing up in the table so everything was good and everything worked.

    Then I changed frmAddAccount to a data entry form, and lost the function of this combo box. The list shows up, but it won't select anything - hence the other 2 fields city and state won't populate.
    I'm also struggling with the 2 subforms that are on here because when I changed it to data entry, the subforms won't come up - no fields just titles.

    That's the scenario and I'm not sure what to do to go back.

    How do I:
    1.) Get 5-digit zip codes from the combo box?
    2.) Get back my multi-field populate using :
    Private Sub ZIP_OnClick()
    ZIP = combo1.Column(0)
    CITY = combo1.Column(1)
    STATE = combo1.Column(2)
    End Sub
    3.) Make a data entry form with 2 subforms
    Last edited by Foskbou; 06-09-10 at 13:07.

  2. #2
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by Foskbou View Post
    I have a form frmAddAccount to enter accounts, name address phone - all that.
    It comes from table tblAccountNames of the same, of 4,500 records that were imported from exel.
    I have a few problems -
    1st.....my zip codes are leading zeros, but they did not import to the table from exel that way. I'm getting 4 digits not 5. Data type is text.
    2nd....for entry purposes, there is a look up of zip codes in my region (tblTownZip) . Field is data type number, and format is set to 00000, so I'm getting the leading zeros
    3rd......Form frmAddAccount has the zip field changed to combo box, where I can select the zip code from a list....the list shows me zip, city and state. The OnClick here populates all 3 fields; but I would only get 4 digit zip codes.

    In trying to correct this - I went back to the tblAccountNames and changed ZIP (text) to a look-up, and they changed to a five-digit zip code, although not the correct one. Somehow the correct one is showing up in the table so everything was good and everything worked.

    Then I changed frmAddAccount to a data entry form, and lost the function of this combo box. The list shows up, but it won't select anything - hence the other 2 fields city and state won't populate.
    I'm also struggling with the 2 subforms that are on here because when I changed it to data entry, the subforms won't come up - no fields just titles.

    That's the scenario and I'm not sure what to do to go back.

    How do I:
    1.) Get 5-digit zip codes from the combo box?
    2.) Get back my multi-field populate using :
    Private Sub ZIP_OnClick()
    ZIP = combo1.Column(0)
    CITY = combo1.Column(1)
    STATE = combo1.Column(2)
    End Sub
    3.) Make a data entry form with 2 subforms
    welcome!


    Lets start with the ZIP code with a missing digit.

    I owudl fix the data to be correct. I woudl run an update query to add the leading zero so that the data is correct.

    Once this is done, retest to see how many issues have been resolved.

    About the table level lookup, please read:

    The Evils of Lookup Fields in Tables
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  3. #3
    Join Date
    Jun 2010
    Posts
    186
    thanks - I vowed I would not do table look-ups putting this together, but lost my marbles.

    I have never used an update query for this type of fix..........can you tell me what it is looking for in Update To or Criteria ?

  4. #4
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by Foskbou View Post
    thanks - I vowed I would not do table look-ups putting this together, but lost my marbles.

    I have never used an update query for this type of fix..........can you tell me what it is looking for in Update To or Criteria ?
    Here is example SQL:

    Code:
    UPDATE Customers SET Customers.ZIP = Format([ZIP],"00000") 
    WHERE (((Len([ZIP]))=4));
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  5. #5
    Join Date
    Jun 2010
    Posts
    186
    Is Customers the form or the table - or both? when I put this in the code of my form frmAddAccount, it gave me a compile error on SET..... am I in the wrong place?

    What about putting ZIP in a query to update? I'm just making things up there, tho - don't know how to write it......I have Update To: and Criteria (access 2007)

  6. #6
    Join Date
    Jun 2010
    Posts
    186
    figured it out, switch to SQL - but it didn't work....I don't get anything in the Where column at all

  7. #7
    Join Date
    May 2010
    Posts
    601
    Make a backup before attempting to run the update query

    Since I do not know your table name or field name, I just created an example.


    The example SQL I posted was created using the Nowthwind.mdb (I think from Access 2003)


    see if this helps:

    Code:
    UPDATE tblMyTableNameHere SET tblMyTableNameHere.MyZIPfieldName = Format([MyZIPfieldName],"00000") 
    WHERE (((Len([MyZIPfieldName]))=4));

    replace tblMyTableNameHere with your actual table name

    replace MyZIPfieldName with your actual field name


    Make a backup before attempting to run the update query
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  8. #8
    Join Date
    Jun 2010
    Posts
    186
    thanks
    I had typed with actual names, the code is right, it just doesn't do anything. I type it in, click view and I see my list of 4-digit zip codes only. 1 column.
    I click design view and the table is there, all names correct, all criteria filled in below just as I wanted
    Nothing happens

    I made a copy of account names, too

  9. #9
    Join Date
    Jun 2010
    Posts
    186
    IT WORKED!!!! I had some disabled mode going on - I didn't know
    I'm so happy :-)

    I'm going to do some test entries (I have a ton of data) and see if I can't fix the multi-box populate somehow. Maybe re-write it (?)

    Thank you so much!

  10. #10
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by Foskbou View Post
    IT WORKED!!!! I had some disabled mode going on - I didn't know
    I'm so happy :-)

    I'm going to do some test entries (I have a ton of data) and see if I can't fix the multi-box populate somehow. Maybe re-write it (?)

    Thank you so much!
    You're welcome.


    Sound slike you are making good progress!
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •