Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2003
    Location
    Paeroa, New Zealand
    Posts
    16

    Question Unanswered: Newbie Needs MAJOR help on Updating Product List!!! URGENT!!

    Soemone help me out on this PLEASE????

    I need to know HOW to create a module or query that will allow me to update my product list from my suppliers latest list.

    The Suppliers list contains four fields...

    NewPrice
    PartNumSKU
    NewDescription
    Supplier

    The Products list contains the same fields (also named the same) and about 30 other fields.

    How do I get the Suppliers Update list (called LatestUpdates) to update my product list (Called Components), but only using this criteria...



    LatestUpdates.PartNumSKU = Components.PartNumSKU
    AND
    LatestUpdates.PartNumSKU = Components.PartNumSKU

    If it finds a match, then it appends the existing values in the Components Table to match those in the LatestUpdates Table

    This is where it gets trickier!!

    I need it to check the values, and if blank insert new values for some of the fields...

    If IsNull(Components.Description) Then
    Components.Description = LatestUpdates.Description
    ElseIf IsNull(Components.ProductName) Then
    Components.ProductName = LatestUpdates.Description
    End If

    Then the rest of the fields are sstandard...

    Components.Supplier = LatestUpdates.Supplier
    Components.PartNumSKU = LatestUpdates.PartNumSKU
    Components.NewPrice = LatestUpdates.NewPrice

    If the function or query DOES NOT find a match, it adds a new record and inserts all fields to the same field names
    AND
    Places a tick in the checkbox "New"

    I also need it to be able to tell if the Latest Updates Table is Newwer than its last update, so that when the next update is done, the tick in the "New" checkbox is removed from the existing records in the Components Table...

    THEN.. I need to check the reversal, If a record is in the Components Table BUT NOT in the LatestUpdates Table, I need it to place a tick in the Discontinued Checkbos on the Components Table for the non matched records.

    Can anyone Help me with this PLEASE??? It is for a private project I am trying to complete URGENTLY!!

    Kind Regards to all

    KiwiMarty (yes, I am a New Zealander :P )
    Though the eyes may falter, the heart strives to fill the gap. A strong heart leads to patience and persistance... Dont count such a visionary out for success!!

  2. #2
    Join Date
    Aug 2003
    Location
    Paeroa, New Zealand
    Posts
    16
    Ooppss.. Made some real errors...

    I know you are supposed to be able to edit the posts, but I cant seem to find the "edit" button (this may be due to my visual impairment however).. Anyway.. here are the corrections to the posting...

    The Suppliers list contains four fields...

    NewPrice
    PartNumSKU
    Description
    Supplier

    The Products list contains the same fields (also named the same) and about 30 other fields.

    How do I get the Suppliers Update list (called LatestUpdates) to update my product list (Called Components), but only using this criteria...



    LatestUpdates.Supplier = Components.Supplier
    AND
    LatestUpdates.

    AS I NEED IT TO MATCH PARTNUMSKU with SUPPLIER!!

    Cheers Again

    KiwiMarty
    Though the eyes may falter, the heart strives to fill the gap. A strong heart leads to patience and persistance... Dont count such a visionary out for success!!

  3. #3
    Join Date
    Nov 2002
    Location
    Belgium
    Posts
    243
    Originally posted by KiwiMarty
    Ooppss.. Made some real errors...

    I know you are supposed to be able to edit the posts, but I cant seem to find the "edit" button (this may be due to my visual impairment however).. Anyway.. here are the corrections to the posting...

    The Suppliers list contains four fields...

    NewPrice
    PartNumSKU
    Description
    Supplier

    The Products list contains the same fields (also named the same) and about 30 other fields.

    How do I get the Suppliers Update list (called LatestUpdates) to update my product list (Called Components), but only using this criteria...



    LatestUpdates.Supplier = Components.Supplier
    AND
    LatestUpdates.

    AS I NEED IT TO MATCH PARTNUMSKU with SUPPLIER!!

    Cheers Again

    KiwiMarty

    I don't mind working out a solution for you, but do you mind posting or sending your db, so it's easier to understand your needs.


    By the way: editing your post can be done on the right lower corner of every post.
    Last edited by artemide; 10-08-03 at 11:15.

  4. #4
    Join Date
    Aug 2003
    Location
    Paeroa, New Zealand
    Posts
    16

    Sample Database for perusal

    Thanks for helpong out here

    Unfortunately I cannot post my entire Database as it is far too large. I have instead posted a Database I downloaded from the internet that did part of what I wanted, and I then editted it to do the rest... It works great if I leave it as it is, however, when I try to import the tables, modules, ect into my own exisinting Database, I get an error when I run it regarding the .NoMatch sequences (something like "Data Type or Varialbe not defined"). And I cant import my own Database to this one, as my main product list contains too many indexes... So it seems I am getting Problems all round!!

    Anyway, the.zip file included is the editted download, and it does EXACTLY what I want regarding the tables, but I also need a means of generating a resulting table that can be sent to another party that has NO IDEA or how to import anything in Access!! So I need to be able to generate a command button that will perform the update by creating a table from the results of the process in the .zip file, into Their Database (which is a replica of my own master database).

    Any help on these issues is GREATLY APPRECIATED!! )

    Cheers

    KiwiMarty
    Attached Files Attached Files
    Though the eyes may falter, the heart strives to fill the gap. A strong heart leads to patience and persistance... Dont count such a visionary out for success!!

  5. #5
    Join Date
    Nov 2002
    Location
    Belgium
    Posts
    243

    Re: Sample Database for perusal

    Attached you will find your sample again.
    It's probably not a ready-to-use solution for you, but I tried to solve it as good as possible.
    I hope this is satisfying.
    If you need further assistance, just let me know.

    In the OnClick event of the button I also wrote a lot of comment.
    READ IT !

    Good luck
    Attached Files Attached Files

  6. #6
    Join Date
    Aug 2003
    Location
    Paeroa, New Zealand
    Posts
    16
    WOW!!! Thanks HeapsArtemide!!

    That is working!!

    NOw, how do I get it to create a table from the newly updated "Components" Table, and create a "Update" Button on a replicated Database, whta will Overwrite the replicated DB's "Components" Table with this one? --> I need this done, because the person using the replicated DB has no idea on what to do in Access, except Data Entry, and I will be located elsewhere so Cant import the table for her. Basically, I need to be able to have the new table (even if held in a temp DB) placed onto her computer - via Download from email - hoping the darn thing will be small enough to do thi, but we are looking at over 600 products). I need her to be able to click the "Update" button on her replicated DB, and have the overwrite happen automatically for her.

    Any ideas on this one??

    SOrry to be a pain, but it is no small project now it seems :P

    Cheers

    KiwiMarty
    Though the eyes may falter, the heart strives to fill the gap. A strong heart leads to patience and persistance... Dont count such a visionary out for success!!

  7. #7
    Join Date
    Nov 2002
    Location
    Belgium
    Posts
    243
    Originally posted by KiwiMarty
    WOW!!! Thanks HeapsArtemide!!

    That is working!!

    NOw, how do I get it to create a table from the newly updated "Components" Table, and create a "Update" Button on a replicated Database, whta will Overwrite the replicated DB's "Components" Table with this one? --> I need this done, because the person using the replicated DB has no idea on what to do in Access, except Data Entry, and I will be located elsewhere so Cant import the table for her. Basically, I need to be able to have the new table (even if held in a temp DB) placed onto her computer - via Download from email - hoping the darn thing will be small enough to do thi, but we are looking at over 600 products). I need her to be able to click the "Update" button on her replicated DB, and have the overwrite happen automatically for her.

    Any ideas on this one??

    SOrry to be a pain, but it is no small project now it seems :P

    Cheers

    KiwiMarty


    What you can do is following :

    Code:
    Private Sub Form_Unload(Cancel As Integer)
        DoCmd.TransferDatabase acExport, "Microsoft Access", "C:\myDestinationDB.mdb", acTable, "OriginalComponents", "ExportedComponents", False
    End Sub
    I suppose she uses a form to add new entries.
    For example everytime she closes her form the Components-table will automatically exported into a database you desire.
    This can be done by using the TransferDatabase-command.

    Or if you really wish her to click a button, then you add one to the form and write the code in the click-event of that button.

    Don't worry about asking questions.
    I'm in the same situation: for the first time in my life they asked me to create a DB. It also started very small, but at the moment it becomes quite complicated.

    If you have some more questions, I will help you searching answers, but the weekend is coming and I don't know if I will find time to read the posts. If not I will be back on Monday.

    See you

  8. #8
    Join Date
    Aug 2003
    Location
    Paeroa, New Zealand
    Posts
    16
    Actually, I need the exact opposite. She uses the database to check prises, an only when neccessary changes them. What I need is the "Export" sequence you gave so that I can export them from my database to a temp one (so that still comes in VERY handy thanks mate :P), so that she can click a button and utilise the updating feature using the new table I created with MY update, or If at possible, is there a way I can make use of an Excel file (as most of my suppliers send me the updates in .xls format) so that both databases use the same .xls file to perform the updats??

    EG: I get an update via downloading from a suppliers website, and use it as the "LatestUpdates" Table for the updating seuence. Then She can also download the same file (or I can email the .xls file to her) and she performs the same update on her replicated database.

    What I would prefer is fot the code to "rename" the existing "LatestUpdates" Table to "OldUpdates" and then "Import" the .xls file to a new "LatestUpdates" Table (for the update sequences - and therefore ensuring it IS using the LATEST UPDATES!).

    This problem get more complex as some of the lines in the .xls file are section headers or none product pricing details (like blank lines, or supplier details, etc) that I dont want included in the "LatestUpdates" Table. So I also need to know how to omit those entries when importing the .xls file...

    Eg: If the lines form the .xls file WERE imported into the "LatestUpdates" Table, I would probably use something like this to omit them from the update sequence...

    If IsNull(rstLatestUpdates!Code) Then
    ...Do Next ...

    So How do I do that BEFORE importing the File (once I know exactly which fields hold the data I need omitted)?

    Is this possible at all?

    Cheers

    Marty
    Last edited by KiwiMarty; 10-11-03 at 19:34.
    Though the eyes may falter, the heart strives to fill the gap. A strong heart leads to patience and persistance... Dont count such a visionary out for success!!

  9. #9
    Join Date
    Nov 2002
    Location
    Belgium
    Posts
    243
    Originally posted by KiwiMarty
    Actually, I need the exact opposite. She uses the database to check prises, an only when neccessary changes them. What I need is the "Export" sequence you gave so that I can export them from my database to a temp one (so that still comes in VERY handy thanks mate :P), so that she can click a button and utilise the updating feature using the new table I created with MY update, or If at possible, is there a way I can make use of an Excel file (as most of my suppliers send me the updates in .xls format) so that both databases use the same .xls file to perform the updats??

    EG: I get an update via downloading from a suppliers website, and use it as the "LatestUpdates" Table for the updating seuence. Then She can also download the same file (or I can email the .xls file to her) and she performs the same update on her replicated database.

    What I would prefer is fot the code to "rename" the existing "LatestUpdates" Table to "OldUpdates" and then "Import" the .xls file to a new "LatestUpdates" Table (for the update sequences - and therefore ensuring it IS using the LATEST UPDATES!).

    This problem get more complex as some of the lines in the .xls file are section headers or none product pricing details (like blank lines, or supplier details, etc) that I dont want included in the "LatestUpdates" Table. So I also need to know how to omit those entries when importing the .xls file...

    Eg: If the lines form the .xls file WERE imported into the "LatestUpdates" Table, I would probably use something like this to omit them from the update sequence...

    If IsNull(rstLatestUpdates!Code) Then
    ...Do Next ...

    So How do I do that BEFORE importing the File (once I know exactly which fields hold the data I need omitted)?

    Is this possible at all?

    Cheers

    Marty

    I have to test it myself.
    But because I have to make a DB myself, my boss won't be very pleased if I spend my time working for you.
    So if you post your excel-file I can use that as my testfile and I will not waste my bosses time creating my own excel-file.
    I can not guarantee I will have a solution within 24 hours, but I'll do my best.

  10. #10
    Join Date
    Aug 2003
    Location
    Paeroa, New Zealand
    Posts
    16
    THnaks heaps mate. I DONT expect you to waste your bosses time, and tothough this is important to me, it is not DIRE urgent. I at least have soemthing that will suffice for now (thanks to your last lot of code :P). Here iw the requested .xls file anyway, and thanks again for your help with all of this

    WHat I need is for the code to omit the "Rubbish" (like the company details and none product related garbage. As you will see when you go through some of the Worksheets, this can be a bit tricky, as some lines have blank fields, but have data that is related to produts (such as product descriptions spread over a few rows) probably the most complicated Worksheets woudl be the "Barebones" and "Laptop" sheets, as these have a row for basic specs, then multiple fows for features (Eg: BareBones have one row for Motherboard Details, and Multiple rows for features, then two or more prices relating to the processor type).

    Heres hoping something can work on this garbage!! LOL

    I have managed to put together a portion of the code I need for this, but I need it to have a nested loop (and I have no Idea HOW to do this), and have the data properly sorted before it is appended or inserted into the relevant table(s)...

    Durrent Dowhile loop...(see below)

    ...If Data is relevant to "This Table" ("Barebones" as an exaple") Add or Append it.
    ...If it has more details to come, store the current data in a varialbe for the next row to be appended to it (such as Motherboard Details appended with Barebones Features, to create a complete description for use in the "Components" Table).
    ......If the data is now complete, send to relevant fields in the "This Table" (such as the "BareBones" Table. AND send the collected Variable (containng the FULL Description - Such as Motherbaord and Features) to the "Components" Table
    ...Elseif the Filed contains Category Titles (such as Optical Drives" Save this data for a varialbe for the following rows until it changes.
    ...Elseif the Filed contains SubCategory Titles (such as "CDrom" Save this data for a varialbe for the following rows until it changes.
    ...Else Reject The Data
    ...etc

    The code Thus Far...

    Private Sub Command42_Click()

    Dim rst As ADODB.Recordset
    Dim strSQL As String
    Dim strPath As String
    Dim strWSht As String
    Dim strWBk As String
    Dim n As Long

    Me.Trial = ""
    Set rst = New ADODB.Recordset

    strPath = "C:\Documents and Settings\Marty\My Documents\JDI Price List AUGUST\Components.xls"
    'the above needs to have a dialogbox added so that the user can select the file from thier hard-drive

    strWSht = "[Components$A1:C8]"
    'need to change the above so that it finds ALL records in the file regardless of size!

    strWBk = "'" & strPath & "'"
    strWBk = strWBk & " ""Excel 8.0;HDR=Yes;"""

    strSQL = "SELECT * FROM " & strWSht & " IN " & strWBk

    rst.Open strSQL, CurrentProject.Connection

    Do While rst.EOF = False
    For n = 0 To rst.Fields.Count - 1
    Me.Trial = Me.Trial & rst.Properties.Count & ": " & n & ": " & rst.Fields(n).name & "=" & rst.Fields(n).Value & ","
    'above is my attempt at trying to get teh actual row number (majorly failed at it of course!)

    Next n
    Me.Trial = Me.Trial & vbCr & vbLf
    rst.MoveNext
    Loop

    rst.Close
    Set rst = Nothing
    End Sub

    the "ME.Trial" relates to a Texbox I created for testing this code
    the

    Although this may seem like a long way to go around this (as apposed to your last lot of code) it is preferable, as I can place all of this in one Function in a Module, so if I have to edit it later for imporvements, I have ONE place to go, and I can understand this better than SQLstrings :P


    Cheers again
    Marty

    Have edited this post to inform you (or anyone else that wants to help) Than I have uploaded an exaple of the tables and process I am currently using to update these tables.

    The Worksheets in the .xls file I have also uploaded relate to the Tables of the Same or similar names, but I need the worksheets (apart from the "BareBones" worksheet) to the "Components Table as wel (in a compressed state)...

    EG: Codes and Prices stay the same
    Descriptions include all specs of the product (EG: Motherboard Comboss have a description that depicts the motherboard, motherboard specs, ram included, processor included, HDD included, etc) as a Compressed Description in the "Components" Table

    Please note that the enclosed Database DOES NOT USE AN EXCEL FILE at the moment, so I need this changed to suit..

    Thanks heaps all
    Attached Files Attached Files
    Last edited by KiwiMarty; 10-16-03 at 22:19.
    Though the eyes may falter, the heart strives to fill the gap. A strong heart leads to patience and persistance... Dont count such a visionary out for success!!

Posting Permissions

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