Hey Guys, created a Function to into our Product Bundles that we use for special services will offer. I have enclosed the Code:

Public Function AddProductToLog(TableName As String, WhereCondition As String, ProductID As Long, Optional Quantity As Long = 1, _
                        Optional ProductBundle As String = "") As Long

    Dim db As Database, rsProduct As Recordset, rsLog As Recordset
    
    Set db = CurrentDb
    Set rsProduct = db.OpenRecordset("SELECT * FROM “ & TableName & “ WHERE WhereCondition)
    Set rsLog = db.OpenRecordset("TableName")

I wonder if this could be used correctly in this way with Declaring the TableName as a String and WhereCondition as a String?

That should absolutely work just fine the way you've got it. One exception - be careful, I see you've got some curly quotes that snuck into your SQL statement. Access doesn't like those, so I don't know if it was a copy-paste error or if you actually put those in your code, but it might generate an error.

Thanks Richard, I removed the quotes around the TableName. But now, I get a 'Compile Error: Argument Not Optional'.

Well, that's because you've got three required parameters: Your table name (which you have there), your WHERE condition (which you have there), but you didn't give it the separate product ID (you're sending just 2 bits of data). I think what you mean to do is have just the WHERE condition. So get rid of the product ID as a parameter because you're sending it in as the WHERE condition.

Oh okay Richard, thanks I will try it. Thanks again.

Richard it work Perfectly, but the rsLog = db.OpenRecordset(TableName)  causing an ERROR.

Well, what do you think that that error means? "Item not found in this collection." Take a moment and think about it. I'll put my answer in the details section below, but see if you can figure it out yourself.

Details

That error tells me you don't have a field named "ID" in your table. Remember, it has to be EXACT.


You know it's funny because I searched for both of the problems that you had in this thread, the argument not optional and the item not found in this collection errors, and I don't have videos for either of these things. And I bet you people search for these a lot. So thank you, you've given me ideas for two new videos.

I have encountered both errors several times so are very familiar to me making the problem a very easy fix. As always, I look forward to these two new videos

James here's something else to consider...

Details

If you don't have a field called "ID" in each of those tables, you can send in the primary key field name as another string parameter, but then you have to refer to it like this in your code:

AddProductToLog = rsLog(IDFieldName)

So if you send in "ProductID" as a parameter that will become

AddProductToLog = rsLog("ProductID")

Which is basically the same thing as:

AddProductToLog = rsLog!ProductID

The different way of writing it allows you to send it in as a text string.


This is all stuff that's covered in my Developer lessons. I see you're only in the Expert classes, so you'll get there soon enough. Keep learnin' :)

Richard, I think that fix the Runtime Error with the rsLog("ProductId"). But for some how I now get an Compile Error: ByRef Argument Type Mismatch with the NativeID.

ByRef Argument Type Mismatch
ByRef v ByVal

Hey Guys, I still could not find the best way. I just put the Code in each Form Code needed for now.