Calling a Microsoft Access Query and Passing Parameters

[Scyclone]

Ah ha, I tricked it. Extended the width to just right at the edge of the dropdowns size. So its column width is equal to the size of the dropdown itself (showingly only one there)

[pbaldy]

Set what to 0? The column widths should look like:

1";0";0";0";...

[Scyclone]

Ah, I had only put in the first size. No worries though, got it working for me now. I definately owe you

I am running into a different issue, not an error, more of trying to figure out how to do this in access.

I have an area for entering in a new entry to the table. Though is there a way to get the IDs for Brand and Category. (On the table, each product has integers for brand and cat id, on their respective tables their ids represent the name)

Actually... I could prob use what you suggested again, including the ID into the select that pulls the brands and categories. Then store them as variables.

=======

dim db as database, strComp as string

Set db = CurrentDb

strComp = "Insert Into ProdList(Name, PartNo, Desc, BrandID, CatID) Values (Me.txtName, Me.txtPart, Me.txtDesc, BrandIDVar, CatIDVar)"

then some means to execute hmm.

Wow my programming skill really has gotten rusty.

[pbaldy]

You can use

CurrentDb.Execute

to execute the SQL. You have to concatenate the form references and variables, with quote delimiters for text values and # for dates. This type of thing:

strComp = "Insert Into (...) Values ('" & Me.txtName & "', " & Me.txtPart & ",..."

[Scyclone]

So

Set db = CurrentDb

strComp = "Insert Into ProdList(Name, PartNo, Desc, BrandID, CatID) Values ('"& Me.txtName & "', '" & Me.txtPart & '", etc etc)"

db.Execute(strComp)

?

[pbaldy]

Conceptually, yes.

[TinaCa]

You can try something a little less complicated. Open the form thenoOpen the Query. In the Criteria for each field you need to pass data to, click on the ... and go to the expression builder. In a little box in the lower left corner are some items click on the plus sign for your database. The go to Forms, Loaded Form, field list and double click on the field that contains the data. (f it is a combo box be sure to type a . (dot) then column(#) i.e. .... productName.column (1). This doesn't always work with combo boxes but it might be a simpler solution that the recordset.

Another option use docmd.runsql (copy the sql code from the sql view in your query. Paste into an event on the form (like ONClick for a button). You will need to replace the criteria with variables. If you send me the query in sql wit the criteria hard coded in the query I may be able to send it back to you with the correct syntax.

[Scyclone]

Hmm... I am not sure what you mean by the expression builder.

Though here is the query I created for the insert.

INSERT INTO ProdList ( ProdName, ProdPartNo, ProdDescription, ProdBrandID, ProdCatID, StatusID )
SELECT [Forms]![Add New Product]![ProdName] AS ProductName, [Forms]![Add New Product]![ProdPartNo] AS PartNumber, [Forms]![Add New Product]![ProdDesc] AS Description, (SELECT ID FROM ProdBrand WHERE [Forms]![Add New Product]![BrandDrop1] = BrandName) AS BrandID, (SELECT ID FROM ProdCat WHERE [Forms]![Add New Product]![CatDrop1] = CatName) AS CatID, [Forms]![Add New Product]![optStatus] AS StatusID
FROM ProdStatus INNER JOIN ((ProdBrand INNER JOIN ProdList ON ProdBrand.ID = ProdList.ProdBrandID) INNER JOIN ProdCat ON (ProdList.ProdCatID = ProdCat.ID) AND (ProdBrand.ID = ProdCat.CatBrandID)) ON ProdStatus.ID = ProdList.StatusID;

[TinaCa]

It looks like you are trying to insert data from the form into the table.

This can be done way easier .
1. Make a form based on the table ProdList (use the form wizard you can make it pretty later)
2. Create a combo box for the field ProdBrand (use the wizard and select the table with the Brand info it and hide the first column - it will make sense when go through the wizard)
3. Create a combo box for the field ProdCat (same as above) and also for any other looks like there might be one for Status(?)
4. Don't remove the text boxes that the form wizard creates for your fields that are in the combo boxes.
2. In the Property Sheet select the tab for Data.
3. Set the Data Entry property to Yes
4. Save the form and then reopen it.

You'll notice that the form fields are all set to null. If you have an autonumber field in the table that will be populated. The user just enters the data and when they navigate away from the form (close it) the data they input is saved to the table.

[Scyclone]

Unfortunately, that isn't really possible given that other rules apply to the ProdBrand and ProdCat, in their case specifically, they can't just be added to like a new product itself could.

Would there be any way to get the SQL insert listed above put into a VBA format for access, that would help me not only on this part but another one down the road that I have already begun setting ground work for .

Thanks for all the help so far Tina

[pbaldy]

I already gave you the basics of getting the SQL into VBA, but here's a tutorial:

http://www.baldyweb.com/BuildSQL.htm

[Scyclone]

Welp heres hoping this works...

====

Private Sub cmdSave_Click()

Dim strSQL As String

strSQL = "INSERT INTO ProdList ( ProdName, ProdPartNo, ProdDescription, ProdBrandID, ProdCatID, StatusID ) VALUES ('" & [Forms]![Add New Product]![ProdName] & "' AS ProductName, '" & [Forms]![Add New Product]![ProdPartNo] & "' AS PartNumber, '" & [Forms]![Add New Product]![ProdDesc] & "' AS Description, (SELECT ID FROM ProdBrand WHERE '" & [Forms]![Add New Product]![BrandDrop1] & "' = BrandName) AS BrandID, (SELECT ID FROM ProdCat WHERE '" & [Forms]![Add New Product]![CatDrop1] & "' = CatName) AS CatID, '" & [Forms]![Add New Product]![optStatus] & "' AS StatusID FROM ProdStatus INNER JOIN ((ProdBrand INNER JOIN ProdList ON ProdBrand.ID = ProdList.ProdBrandID) INNER JOIN ProdCat ON (ProdList.ProdCatID = ProdCat.ID) AND (ProdBrand.ID = ProdCat.CatBrandID)) ON ProdStatus.ID = ProdList.StatusID;"

CurrentDb.OpenRecordset strSQL

End Sub

[Scyclone]

Hmm, did I do the quotes in the wrong order?

==

Run-time Error '3075':
Syntax error (missing operator) in query expression "Test Product 2' AS ProductName'.

[pbaldy]

Add this after the string is built:

Debug.Print strSQL

That will print out the finished SQL to the VBA Immediate window. You can examine it there, or copy/paste to a new query to test it, where you'll often get a more descriptive error. If you don't spot the problem, post the SQL here. By the way, in a VALUES clause you don't need the field aliases ("AS ProductName").

[Scyclone]

It wouldn't pop up the string for me to analyze it so I added in a msgbox to display it.

Here is what it shows. (Ill remove the As portions, Access had originally done that when referencing form items)

INSERT INTO ProdList ( ProdName, ProdPartNo, ProdDescription, ProdBrandID, ProdCatID, StatusID ) VALUES ('Test Product 2' AS ProductName, 'S2222' AS PartNumber, 'Test' AS Description, (SELECT ID FROM ProdBrand WHERE 'Brand A' = BrandName) AS BrandID, (SELECT ID FROM ProdCat WHERE 'Category A' = CatName) AS CatID, '1' AS StatusID FROM ProdStatus INNER JOIN ((ProdBrand INNER JOIN ProdList on ProdBrand.ID = ProdList.ProdBrandID) INNER JOIN ProdCat ON (ProdList.ProdCatID = ProdCatID) AND (ProdBrand.ID)) ON ProdStatus.ID = ProdList.StatusID;