Results 1 to 15 of 15
  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Looking for the best way to do this.... (AXP)

    Hello All,
    I have a function I would like to do with a query and form. Basically, I want to create some entries in an Access table by running a query against an ODBC source. The hard part, I think, will be to only allow the entries to be created one time. In other words, if I run the query the first time it will return three rows for instance. In those three rows will be a part number, sales order, and line item number. The combination of these numbers makes the item unique. So, I want to create some entries for these items in my tblPartWO table. To that I will allow a user to add notes about that line item. Beyond that, I wouldn't want there to be any duplicate entries in this process. I have played a bit with the append query but that doesn't seem to really get it done. Has anyone out there done this sort of thing before?

    Thanks,
    Mark

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Looking for the best way to do this.... (AXP)

    If you set a composite index on Part Number, Sales Order and Line Number, and make this index unique, Access will refuse to add duplicates to the table. One way to set such an index is to select the three fields in the Table design window, and click the Primary Key button on the toolbar.

  3. #3
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Looking for the best way to do this.... (AXP)

    Thanks Hans,
    I can do the index however, this table is existing already and there are many rows that have no data on one of the perspective keys. I could update the empty fields with something like "0" but then there will be duplicate keys. Got some ideas about how to overcome this problem? I suppose I could go with a new table.

    Thanks,
    Mark

  4. #4
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Looking for the best way to do this.... (AXP)

    OK, I may just kill the original table since it didn't have any real good data anyway. I tried the composite key you mentioned and that seems to work. Is there a way that I can keep any errors from popping up when the query runs? Basically, I just want it to run in the background and add rows as needed and skip over the duplicates.

    Thanks,
    Mark

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Looking for the best way to do this.... (AXP)

    Set the Ignore Nulls property of the index to Yes, Access will only verify that records with values in all three fields are unique, you can have any number of records with incomplete values in the key fields. You can't set Ignore Nulls to Yes for a Primary Key, but you can for a Unique key.

  6. #6
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Looking for the best way to do this.... (AXP)

    OK, how do you set a unique key then? I see where you can set up an index and there is a place for the primary key. I set the other three fields in the index and ignore nulls. It gives me a key violation when I try to run the query.

    Thanks,
    Mark

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Looking for the best way to do this.... (AXP)

    The Indexes windows should look like this (the English captions are fake, the dialog was entirely in Dutch)
    Attached Images Attached Images
    • File Type: png x.PNG (11.3 KB, 0 views)

  8. #8
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Looking for the best way to do this.... (AXP)

    OK, that is what I am playing with. I guess I am doing it wrong. I will play some more......

    Bedankt

  9. #9
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Looking for the best way to do this.... (AXP)

    OK, I have it now. I have never played with that before. How do I keep an error from popping up when the query is run? This will happen when they open the work order and there are already entries in there.

    Thanks,
    Mark

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Looking for the best way to do this.... (AXP)

    If you execute the query or SQL string using

    CurrentDb.Execute "query_or_sql", 128

    and use error handling, you can trap error 3022. (If you have a reference to the Microsoft DAO 3.6 Object Library, you can use the DAO constant dbFailOnError instead of the literal value 128.)

    Sub DoAppend()
    On Error GoTo ErrHandler

    CurrentDb.Execute "query_or_sql", 128
    Exit Sub

    ErrHandler:
    Select Case Err
    Case 3022 'duplicate key error
    ' Ignore
    Case Else
    MsgBox Err.Description, vbExclamation
    End Select
    End Sub

    "query_or_sql" must be replaced by the name of an append query or by the SQL string of an append query.

  11. #11
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Looking for the best way to do this.... (AXP)

    OK, so now I am over the unique key problem and the other issue I had today which was that pesky ODBC call error. One last item for concern is this..... I have the append SQL string adding records to my Access table when there are no records present in the access table. However, the idea behind this is to "Update" the table with any new items that happen to come along since the last time it was opened. How this works is this: The tech opens a work order. The append query looks into the SQL table to see if anything has been listed against a sales order. If so, it adds an entry for each item into the access table. If the tech opens the work order tomorrow and an additional item gets added, I need that item to also be added to the Access table.

    My current query will not work this way. It looks for all matches and spits out a RTE if it sees anything already in the access table. How can I just "Add" items that aren't there yet?

    Thanks,
    Mark

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Looking for the best way to do this.... (AXP)

    Mark,

    You will have to decide how you can identify items that are already present in the Access table - is that just a matter of looking at the key fields?

    When you know how to identify those records, you must modify the query to exclude them. As an exercise, use the Find Unmatched Query Wizard and look at the design of the query produced by it.

  13. #13
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Looking for the best way to do this.... (AXP)

    OK, thanks. This is the first time I have had to do something like this so it will be a good learning experience.

    I'll let you know how it turns out!

    Thanks,
    Mark

  14. #14
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    527
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Looking for the best way to do this.... (AXP)

    Wow, I think I am making good progress with this now...... I have managed to build an SQL string that finds unmatched items in two tables and then performs an append to the destination table. What I need to now is to paste in another value that is not located in either tables. Is it possible to do something like this? (where strWO is a value previously set):

    strSql = "INSERT INTO Parts_WO ( Part_so_number, Part_Line_Item, Part_Number, Part_Qty, Part_Description, part_wo_id = strWO ).........

    Thanks,
    Mark

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Looking for the best way to do this.... (AXP)

    INSERT INTO has two forms:

    INSERT INTO TableName (Field1, Field2, ..., FieldN) SELECT Field1, Field2, ..., FieldN FROM OtherTable WHERE Condition

    and

    INSERT INTO TableName (Field1, Field2, ..., FieldN) VALUES (Value1, Value2, ..., ValueN)

    Your SQL string doesn't conform to either. But in both forms, you can insert literal values, for example

    strSQL = "INSERT INTO Table1 (Field1, Field2) SELECT Field1, " & _
    Chr(34) & strVariable & Chr(34) & " AS Dummy FROM Table2"

Posting Permissions

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