Results 1 to 5 of 5
  1. #1
    Join Date
    May 2006
    Posts
    9

    Unanswered: Update table with combobox value

    I have a table called PartNumbers. It contains the fields part number, description, plating, and a few others. I recently added a new field with two possible values, 4510 or 4516. Those values are linked to a table through a combobox. How can I quickly fill in those new values to the PartNumber table.

    For example, I know that all parts under the number 20000 are 4510. The part number is in text format (it contains a few hyphens in some cases: 17818-05) The field has a relational integrity to the table with 4510 numbers. I tried many update queries but was unsuccessful.

    I'm 3 weeks new using Access 97.

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    If you only have two possibilities (4510 or 4516) the best approach would probably be to use an option group. If you're unfamiliar with this, just go to help and enter "option group."
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Nov 2003
    Posts
    1,487
    Here's a poke at it..and since you neglected to inform us what the name of this new field is, I'll make one up....NewField:

    Backup your Table! Place the following into the OnClick event of a Command Button (somewhere):
    Code:
    Dim SQLstrg As String
     
    ' Suppress the Update Message from Access.
    DoCmd.SetWarnings False
     
    ' For the 4510 insertion...
    SQLStrg = "UPDATE PartNumbers SET NewField=4510 WHERE CLng(Val(IIF(Instr" & _
                 "(PartNumber, ""-"") > 0,Left$(PartNumber, Instr(PartNumber, ""-"") " & _
                 "- 1), PartNumber))) < 20000"
    DoCmd.RunSQL SQLstrg
     
    ' For the 4516 insertion...
    SQLStrg = "UPDATE PartNumbers SET NewField=4510 WHERE CLng(Val(IIF(Instr" & _
                 "(PartNumber, ""-"") > 0,Left$(PartNumber, Instr(PartNumber, ""-"") " & _
                 "- 1), PartNumber))) > 19999"
    DoCmd.RunSQL SQLstrg
     
    ' Reinitiate Access Messages.
    DoCmd.SetWarnings True
     
    MsgBox "***   D O N E   ***"
    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  4. #4
    Join Date
    May 2006
    Posts
    9
    Thank you CyberLynx. After a couple of hours modifying what you wrote it worked.

    This is what I ended up with (change in red):

    Dim SQLstrg As String

    ' Suppress the Update Message from Access.
    DoCmd.SetWarnings False

    ' For the 4510 insertion...
    SQLstrg = "UPDATE PartNumber SET InternalAcctNoId=[InternalAcctNo].[Column(1)] WHERE CLng(Val(IIF(Instr" & _
    "(PartNumber, ""-"") > 0,Left$(PartNumber, Instr(PartNumber, ""-"") " & _
    "- 1), PartNumber)))>29999"
    DoCmd.RunSQL SQLstrg

    ' Reinitiate Access Messages.
    DoCmd.SetWarnings True

    MsgBox "*** D O N E ***"

    Explaination:
    Column (1) = 4510
    Column (2) = 4516 **I reused the same code and changed the column number. I deleted the second half of your code for troubleshooting. It asked me for parameter and I guessed 1 the first time and 2 the second time running the code with changes.

    The last part "greater than" is different to match my needs. In reality the update requirements were as follows.

    0-19999 = 4510
    20000-29999 = 4516
    30000 and above = 4510

    Thank you very much for getting me there. I will learn to provide better info next time

  5. #5
    Join Date
    Nov 2003
    Posts
    1,487
    Quote Originally Posted by jalviso
    I will learn to provide better info next time
    Indeed...you most likely could have saved yourself another couple hours of headache. This only goes to show EVERYONE how important it is to provide adequate details about the task or problem at hand.

    I'm glad I was of some assistance to you.

    Cheers...

    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


Posting Permissions

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