Copying Record from a continuous subform to another subform with command button

[Kipandrews06]

I'm having a difficult time figuring this out. I have a form with two subforms that's in a tab page section. One subform is on the first tab which is a continuous subform that displays a product list that you can scroll through and view all the products. I have a command button that shows up on each record that says "Add". Then on the next tab, I have another continuous subform that is the Item Cart. So what I want to do is on the first subform with the product list, any product they click the "Add" button on, that it would copy that existing record and save it to the Item Cart where when they click on the Item Cart tab, all the products they added from the button would be there in that subform. I'm pretty sure doing an append query through SQL is the way to go, but I can't figure it out, nothing is working. And if I can get that to work properly, I want to save all the records from the Item Cart subform to a "ProductOrders" table and then delete the records in the Item Cart. So here's my setup so far:



Main Form is called "OrderDetails" that has the tabs with the two subforms
Subform 1 is called "ABCOutdoor_ProductList" which is linked to a query based on a table called "Products" that just shows the products for the company ABC Outdoor.
Subform 2 is called "ItemCart" which is linked a table called "ItemCart"
My command button on the continuous subform 1 is called "cmdAdd"

any ideas or suggestions on how to accomplish this?

[Welshgasman]

Use Insert SQL to copy the data from one source to the other?

[Micron]

I would have the Add button add a record (Append query or vba sql) to a staging table, using the PK value for the product as the unique id for products in the cart along with whatever fields are required for the order and its line item records. Deletions from the cart would Delete records from the staging table. Committing the order would copy (append) to the sales table and delete the record(s) from the staging table. Maybe Google Append and Delete queries from forms if you don't know what that means.
EDIT - the staging table should not need an autonumber field.

[Kipandrews06]

Yeah I want to definitely try that method, but the examples I've found online to append and use the INSERT function doesn't seem to work with what I've tried. This is what I've tried so far:

Dim strInsert As StringDim db As DAO.Database


strInsert = "INSERT INTO TblItemCart(ID,[Product Code],[Amt In Stock])" & _
            "SELECT ID,[Product Code],[Amt In Stock]" & _
            "FROM ABCOutdoor_ProductList" & _
            "WHERE ID = " & ABCOutdoor_ProductList.Form.txtID
            
Set db = CurrentDb()


db.Execute strInsert, dbFailOnError
Set db = Nothing




End Sub

I keep getting an error message. Any idea what I may be doing wrong? Do I have this totally wrong or am I on the right track?

[Micron]

Whatever this is
ABCOutdoor_ProductList.Form.txtID

it doesn't look right. You'll have to provide more than "I get an error" (which one of the 2 or 3 thousand error messages is that?) and explain what your references are.

[Kipandrews06]

Sorry, I should have been more specific. The error message I'm getting is "Runtime Error 424: Object Required"
The code I found online somewhere used this except their forms and tables were different. I tried to incorporate it to my forms but obviously I didn't do it right. So my references are:

Main Form is called "OrderDetails" that has the tabs with the two subforms
Subform 1 is called "ABCOutdoor_ProductList" which is linked to a query based on a table called "Products" that just shows the products for the company ABC Outdoor.
Subform 2 is called "ItemCart" which is linked a table called "ItemCart"
My command button on the continuous subform 1 is called "cmdAdd"

The fields I want to copy and add from the ABCOutdoor_ProductList (subform 1) are called "Product Code" and "Amt In Stock". The ID I have in the code is just referring to the ID (PK) of each subform. The table I have called ItemCart has fields that are identically called Product Code and Amt In Stock.

[CJ_London]

I presume 'Dim strInsert As StringDim db As DAO.Database' is a copy/paste error

otherwise looks to me like you don't have any spaces before SELECT, FROM or WHERE

Suggest debug.print strInsert to see the created code in the immediate window, then copy/paste to a new query to see if it runs

[Welshgasman]

I presume 'Dim strInsert As StringDim db As DAO.Database' is a copy/paste error

I've noticed that this site does that a lot when you paste code into a code window?

[Welshgasman]

Yeah I want to definitely try that method, but the examples I've found online to append and use the INSERT function doesn't seem to work with what I've tried. This is what I've tried so far:

Dim strInsert As StringDim db As DAO.Database


strInsert = "INSERT INTO TblItemCart(ID,[Product Code],[Amt In Stock])" & _
            "SELECT ID,[Product Code],[Amt In Stock]" & _
            "FROM ABCOutdoor_ProductList" & _
            "WHERE ID = " & ABCOutdoor_ProductList.Form.txtID
            
Set db = CurrentDb()


db.Execute strInsert, dbFailOnError
Set db = Nothing




End Sub

I keep getting an error message. Any idea what I may be doing wrong? Do I have this totally wrong or am I on the right track?

You really should debug.print the sql string to see what you get, rather than what you think you have got?
That will show a lot of errors if they exist?

Plus you can then copy that to a SQL window and try and run it, to see what you actually get in regards to data.?

[Micron]

[/COLOR]

I've noticed that this site does that a lot when you paste code into a code window?

I agree, so I watch out for it now if I'm pasting code. When it puts code on the same line as beginning tag I always move it to the next line. That tends to make it more obvious.
Perhaps by doing so I've assumed others didn't bother to separate, but it sure would explain the error message in this case. It would cause the Set db line to give that error.

@Kipandrews06 - suggest you get in the habit of putting the space at the beginning of the sql line as it's much easier to see that it has been done.

[Kipandrews06]

Yeah that was definitely a copy/paste error, I didn't catch that. It really is typed like this:

Dim strInsert As String
Dim db As DAO.Database

This is probably some really dumb questions on my part, I'm still a newbie to vba code and SQL stuff and don't fully understand everything yet. So how do I "debug.print" to see all the errors? I tried typing it into my code but it didn't do anything. I put it at the bottom above "db.Execute strInsert" and then tried below it and neither time it did anything. It still just kept giving me the same "Runtime Error 424". And my next dumb question is @Micron: what do you mean by I need to put a space at the beginning of the SQL line?

[Welshgasman]

I would comment out the Execute until you have the syntax correct? That is how I tend to do it. Use a ' at the start of the line. ? is shortcut to output/print the result of the statement
? Date()

The Debug.Print will output to the Immediate Widow (Ctrl + G). In that window you can type in ? Me.WhateverThe NameOfTheControl is on the form (if the form is open)
Also good for just testing simple things like DLookUp() and getting the syntax correct again.

I believe Micron means

strInsert = "INSERT INTO TblItemCart(ID,[Product Code],[Amt In Stock])" & _
            " SELECT ID,[Product Code],[Amt In Stock]" & _
            " FROM ABCOutdoor_ProductList" & _
            " WHERE ID = " & ABCOutdoor_ProductList.Form.txtID

Some people put the space at the end

strInsert = "INSERT INTO TblItemCart(ID,[Product Code],[Amt In Stock]) " & _
            "SELECT ID,[Product Code],[Amt In Stock] " & _
            "FROM ABCOutdoor_ProductList " & _
            "WHERE ID = " & ABCOutdoor_ProductList.Form.txtID

and whilst it does not make much difference here in this code, when you have a humongous sql statement, you need to scroll to the right to see if a space exists.
Putting it at the front negates that scrolling?

I also prefer at the beginning.

HTH

Edit: The link in my signature shows how to debug Access.? I haven't put it there just because I like links?
One of the videos when you get there is this https://www.youtube.com/watch?v=7LQ1l6jdN2Q

[Micron]

I haven't put it there just because I like links?

LOL! I suspect signatures are the most overlooked thing in a post. Next would be the questions we ask to seek clarity (not that Kip has done that here).
So back to the error message. If the Dim lines are correct as stated, then the "Object required" error must be what I alluded to before:
ABCOutdoor_ProductList.Form.txtID looks like an incorrect object reference.

re the other stuff:
I'm not going to follow those links to see if I'm restating something here: also check the syntax of sql strings, especially when they don't work -

?strInsert

Common practice is to do that (or write debug.print strInsert in code) and copy and paste into new query sql view and switch to datasheet view to see if it balks. I recommend datasheet view because it won't execute action queries but will validate the sql. If Access balks, it usually stays in sql view and highlights the offending portion (or something next to it). Watch out for forgetting to comment out debug statements when you're done.

Also, good habit to get into is set object variables to Nothing at the end. Memory space used by the object may or may not be recovered when the procedure terminates. It depends on factors that are involved in Access' garbage collection. I'll review the object references provided earlier, see if I can figure it out and post a sample using an error handler and why.

[Micron]

Subform 1 is called "ABCOutdoor_ProductList"

I think that subform means the subform control to you. A lot of people refer to the control as the subform and that's OK; I just prefer to avoid ambiguity when I can. So try

Me.ABCOutdoor_ProductList.Form.txtID

as that mirrors the syntax of

Forms!subformControlNameHere.Form.subformObjectNameHere
or
Forms!subformControlNameHere.Form.subformPropertyHere
or
Forms!subformControlNameHere.Form.subformMethodHere

where Me. replaces Forms!

One way to write with an error handler:

Dim strInsert As String
Dim db As DAO.Database

On Error GoTo errHandler

'strInsert = "INSERT INTO TblItemCart(ID,[Product Code],[Amt In Stock])" & _
            " SELECT ID,[Product Code],[Amt In Stock]" & _
            " FROM ABCOutdoor_ProductList " & _
            " WHERE ID = " & Me.ABCOutdoor_ProductList.Form.txtID
            
'Set db = CurrentDb()
'db.Execute strInsert, dbFailOnError

exitHere:
Set db = Nothing
Exit Sub

errHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume exitHere

[Kipandrews06]

Thank you all for the tips and advice. I will look at those links and try more trouble shooting with the debug.print method. @Micron- I changed that ABCOutdoor_ProductList.Form.txtID and added Me. in front of it like you suggested along with the other error codes above and it gave me an error message that said "Compile error: Method or data member not found" and it highlighted Me.ABCOutdoor_ProductList.Form.txtID. I will go and look at those links though and try some more things and see if it starts working. I appreciate your alls help on this, I've been stuck on it for over a week now and I'm starting to pull my hair out lol.