Populating table records using VBA

[MikeWaring]

Help!! I've been getting some help from a programmer to help me fill in blank cells in a table; I think the code will be self explanatory to most VBA competents (I'm a complete novice, hence the need for further help!), but what I need now is to expand the code to make it populate more fields in that same table.



As some background info, we have a db that imports and processes our daily sales orders. The raw data originates from a csv file downloaded from Ebay and is imported into the db in a table called "Import data From Ebay - MIDS TOOLS".

If a customer buys more than one item at the same time, the Ebay data has a particular way of populating the data fields in this table. For each of these "Multi Item"orders, it populates several rows in the table Basically, the first row contains the customer's name and address details and the total value of the order. The next rows immediately after this contains the details of the actual items bought, but some of the customer's name and address details are left blank. So as an example, if a customer buys 2 items in one order, there will be 3 rows in the table, one containing his address details and the other 2 rows containing the details of the actual items purchased.

This is causing issues in our system, so I need to effectively copy and paste the personal details from the 1st row into those other 2 rows.

So far, the code looks at the table, identifies those records that have data missing, then does a "MakeTable" (tbl_Customers) that contains the data that is to be used to populate the first table, then it re-popluate that first table.

There are 2 fields that are populated in both the personal details and the item details rows - the "Sales Record Number" and the "Buyer ID" fields. I've attached a sample table for 2 customers. I've changed the personal details so as not to breach any data protection laws.

At the moment, it populates one blank column called "Buyer Full name" in the table called "Import data From Ebay - MIDS TOOLS" - I need to expand this code to also populate the following columns in that same table:-

Buyer Phone Number
Buyer Address 1
Buyer Address 2
Buyer Town/City
Buyer Postcode
Buyer Country

Presumably I can do this by copying & pasting certain parts of the code text, then changing the relevant column names to suit but I haven't the foggiest clue of which text to copy.

Here's the code as it stands:-

'Add names to the rows that have missing names.

'First check to see if there are any new customers in the table "Import data From Ebay - MIDS TOOLS" that are not in tbl_Customers. If there
'are, they are added to the table.
CurrentDb.Execute "INSERT INTO tbl_Customers ( [Buyer Full name], [User Id] ) " & _
                "SELECT [imp].[Buyer Full name], [imp].[User Id] " & _
                "FROM [Import data From Ebay - MIDS TOOLS] AS imp LEFT JOIN tbl_Customers ON " & _
                "[imp].[User Id] = tbl_Customers.[User Id] " & _
                "WHERE ((([imp].[Buyer Full name]) Is Not Null) AND ((tbl_Customers.[User Id]) Is Null)); ", dbfailonerror



'Update the missing name entries in the imported table to the corect names for the user id
CurrentDb.Execute "UPDATE [Import data From Ebay - MIDS TOOLS] " & _
                "INNER JOIN tbl_Customers ON [Import data From Ebay - MIDS TOOLS].[User Id] = tbl_Customers.[User Id] " & _
                "SET [Import data From Ebay - MIDS TOOLS].[Buyer Full name] = [tbl_Customers].[Buyer Full name]; ", dbfailonerror


MsgBox "Import process is complete", vbInformation

I hope I've explained myself well enough, but if not please someone let me know!

Kindest regards
Mike

[ssanfu]

I added a reference to Microsoft DAO 3.6 Object Library. In the code, dbfailonerror won't work without the reference. You weren't seeing it because you didn't have "Option Explicit" as the 2nd line at the top of the module.

I also turned off "AutoCorrect" - a known cause of corruption.

To add the data to the additional fields in table "Import data From Ebay - MIDS TOOLS", the same fields in "tblCustomers" MUST be filled in.

I deleted all entries in "tblCustomers". The code then adds the users and the data, then updates the table "Import data From Ebay - MIDS TOOLS".

Check the tables, then open Form1. Check the tables again. (this was kinda fun)

[ssanfu]

I just realized the code is updating the "Buyer Country", but not the "Buyer County".

Is this a problem?

[MikeWaring]

Hi Steve, thanks for your rapid response. I'm just closing down for the night, (Its nearly midnight here in the UK) so I'll have a look at your changes tomorrow. In regard to your last post re the "Buyer County" field, I was originally not too fussed about it, but on reflection it would be good to include that as well. Could you add that for me?
Thanks again for your valuable help!
Kind regards
Mike

[ssanfu]

OK... but I wouldn't do it for just anyone.


Well... actually, I probably would..