Need Help With INSERT INTO In VBA For My DB

mboyde

Registered User.
Local time
Today, 16:14
Joined
Jul 6, 2014
Messages
21
Hello all,
This is my first post on this site, but I have used this site on numerous occasions to get solutions to my problems. But as every good programmer knows, the more you build the more complex things in your DB gets. I would consider myself quite adept at VBA, but I have no idea how to solve my problem:
I have 4 (pertinent to the question) tables. These tables are: "ExistingClientTable", "ExistingClientContactTable", "ExistingClientAddressesTable", and "ProspectiveClientTable".

The "ExistingClient...." tables have a relationship under the field "File Number" in which the primary key (also named File Number) is located in the "ExistingClientTable".

The "ProspectiveClientTable" has a primary key of its own for good reason.

I would like to create a button that creates a new record where fields from ProspectiveClientTable = ExistingClientTable, which also generates a new File Number.

For the rest of the data in the ProspectiveClientTable, I would like to append those records, automatically fetching the last File Number created.

I've looked around for solutions to my problem, but cannot seem to find them anywhere. I would LOVE any help on this, as this is beginning to give me a headache to resolve. :banghead:
 
Thank you pbaldy for such a quick response. I believe that the Allen Browne link that you provided is for tables that have relationships to each other. My problem is occurring because I have two different, unrelated tables in which I am attempting to transfer data between. The tables not bearing the primary key are the problem, because I'm not sure of the best way to write the code in order to fetch the last record that was made in the ExistingClientTable (where the primary key resides) and put that into the strSQL in order to accomplish this.
 
Well, the link is for related data and I thought that might be where you had a problem. If the data is on a form that you want your query can use that:

INSERT INTO...
SELECT...
FROM...
WHERE KeyFieldName = Forms!FormName.TextboxName

If you simply want the "last" record, you could use a DMax() to get it and then use the result in your code.
 
This is the code that I have currently (attached). Sorry, the site would not let me use the ['CODE] portion of the (assuming BB-code) options. I think my syntax is off somewhere....
 

Attachments

I think in essence you're doing what the link describes: inserting a "main" record, getting its ID number, then using that number to insert child records. You populate ExistingClientMAX before running the SQL statement that would have created it, and you can't run all the SQL statements at the end, as some depend on getting a value after the previous one is run (as above).
 
So, if I wanted all the "related" (linked) records moved, I'd have to create a separate button with the SQL/VBA behind each one?
 
Also, the line on the Allen Browne code that says "!CustomerID = Me.CustomerID": Could I refer to my existing client table through that?
 
So, if I wanted all the "related" (linked) records moved, I'd have to create a separate button with the SQL/VBA behind each one?

No, I would expect a single button on the main "parent" record, which would copy the parent and data from related child tables.

Yes, you would replace his references to table/field names with yours.
 
I may have not mentioned this before, but the Existing and prospective client tables are not linked in any way whatsoever. I still can't figure this out. It's driving me mad because I usually don't have this much trouble with any VBA code.
 
They don't need to be; the expectation is that there's a relationship between the 3 "Existing..." tables. I had assumed that the same tables existed on the prospective side, but it appears not? That changes the situation, but not dramatically. In my view the trick is still getting the new key from ExistingClientTable so you can use it when inserting into its child tables. You were getting it with a DMax(), Allen used:

.Bookmark = .LastModified
lngID = !OrderID

which is also how I do it (DMax() could be unreliable in a multiuser situation).
 
Yeah, the existing client tables are all linked with [File Number] as the primary key. The DB is split and set up for multi-user but the chances that more than one user would be using this command are slim to none.

So the .AddNew refers to controls on the loaded page right?
 
In Allen's code? Yes, he's referring to the form's recordset. Your code isn't far off, I think you just need to execute the first insert, then get the ID, then do the other inserts. You get the ID before you've inserted the record.
 
OKAY! I got it to work up to it asking "You're about to append 1 row of data....blah blah blah" using my original code moved around. Do you think that my DMax would be appropriate for my setup?
 
DMax() is probably fine unless you're in a high-volume environment. The risk is that somebody inserts a different new record between the time you insert one and get the max number (you'd get the other person's record). Given the speed your code will get the new max, there would only be a very small window of time when that could happen (milliseconds).
 
So, I tried it and it asked for ExistingClientAddressesTable.[File Number] parameter value. Does it matter if I use (.) or (!)?
 
In the last couple of SQL statements? You're getting that because the table isn't specified in the FROM clause. Because there's no relationship, does that second criteria need to be there? I would think you only need the

WHERE ProspectiveClientTable.[Prospective Number] = " & Me.[Prospective Number]
 
By the way, watch out for your spacing. Things like:

"FROM ProspectiveClientTable" & _
"WHERE ProspectiveClientTable.[Prospective Number]

Turns into

FROM ProspectiveClientTableWHERE ProspectiveClientTable.[Prospective Number]

Note the lack of space before WHERE. Sometimes Access will figure it out, sometimes not. Best to include a space on one of the lines to make sure.
 
So you're saying that:
Code:
PTEAddressInfo = "INSERT INTO ExistingClientAddressesTable([Street],[City],[State/Province],[Country],[Zip Code])" & _
                          "SELECT Address, City, State, Country, ZIP " & _
                          "FROM [ProspectiveClientTable] " & _
                          "WHERE ProspectiveClientTable.[Prospective Number] = " & Me.[Prospective Number] & "" & _
                          "AND ExistingClientAddressesTable![File Number] = " & ExistingClientMAX

DoCmd.RunSQL PTEAddressInfo

I don't need which statement? I need the first statement to specify the record that I'm currently on, because without it I would end up moving all applicable records, right? The second one I need to specify which file number that the record will be moved to (in order to make the relationship between the tables to work)
 

Users who are viewing this thread

Back
Top Bottom