CODEWITHSUNDEEP
sqlms-accessms-access-2007
ackdaddy
ackdaddy

Reputation: 131

Reference a field on a form within a query using SQL

I have an Access 2007 database that will be housing tables which refer to the bill of materials for multiple products. On the main form I want a user to be able to select one of the products - OK, easy. Now, I want two queries to run once they press a button after choosing their product from a dropdown. The first query is a simple delete query to delete all information on a table. The second query is where I'm having my issue with my SQL syntax. I want the information from a static table to be appended to the table where the delete query just removed everything from.

Now, each table that houses the bill of material for each product is labeled with the product's name. So I want the dropdown (combo0) to be the reference point for the table name in the FROM clause within the SQL string. Code is as follows:

INSERT INTO tblTempSassyInfo (Concat, TableName, AddressName, PartNumber, [L/R], FeederSize, QtyPerBoard, SASSYname, RawBoard)
SELECT TableName & AddressName & PartNumber, TableName, AddressName, PartNumber, [L/R], FeederSize, QtyPerBoard, SassyName, RawBoard
FROM [FORMS]![DASHBOARD]![Combo0];

So you can see where I'm trying to reference the product name in the dropdown on the form as the table name. Please let me know if this is possible.

Upvotes: 3

Views: 25331

Answers (5)

HansUp
HansUp

Reputation: 97101

"... I'm trying to reference the product name in the dropdown on the form as the table name. Please let me know if this is possible."

It is not possible with Access SQL.

The db engine can only accept the actual table name --- it isn't equipped to reference a form control to find the table name nor to accept any other type of parameter to obtain the table name.

You could change the query to include your combo's value as the table name and then rewrite the SQL from the combo's after update event.

"SELECT * FROM [" & [FORMS]![DASHBOARD]![Combo0] & "]"

A similar approach could keep Access happy. But it may not be the best fit for your application.

Upvotes: 1

Robert Trent
Robert Trent

Reputation: 21

This is 100% possible in MS Access 2010 onward based on my experience. I've not used 2007, but MS says it is possible (see link below). I'm using parametrized queries in a few databases.

PARAMETERS  [forms].[dash].[dt_val] DateTime; 
SELECT a.F3 AS AdEnt, [forms].[dash].[dt_val] AS Expr1...

The important thing I've found is using a form the user will be interacting with and setting the Date as "DateTime" within the parameter. Here is a video from Microsoft that shows how to and says that it applies to 2007.

Use Parameters in MS Access Queries

Additionally, if you want to do a delete or append, save it as a query then place a button on the form that executes the docmd.runquery for the name of that saved delete/append query.

Upvotes: -1

Cahaba Data
Cahaba Data

Reputation: 622

As was stated; Access (and just about any brand database) can definitely do append and delete queries.

The problem is the design. Specifically: FROM [FORMS]![DASHBOARD]![Combo0];

From clause must be a record set (table) not a call to a control on a form.

My suggestion is to first establish a Select query that has the correct data that you want to append. Save that with a name. You need to be able to do this first.

Once that is done - then create an Append query that uses that saved Select query as its starting record set.

You then just need to trigger the Append query (the Select query will automatically run) using vba behind your button click event: Docmd.OpenQuery "Append Query Name"

Upvotes: 0

Johnny Bones
Johnny Bones

Reputation: 8402

You're pressing a button to do this. This implies that some VBA code is running behind the scene (the Click event of the button). In that case, the answer is a resounding Yes.

Dim strSQL as String
Dim strSQL2 as String

strSQL = "DELETE * FROM tblTempSassyInfo;"
DoCmd.RunSQL (strSQL)

strSQL2 = "INSERT INTO tblTempSassyInfo (Concat, TableName, AddressName, PartNumber, [L/R], FeederSize, QtyPerBoard, SASSYname, RawBoard)
SELECT TableName & AddressName & PartNumber, TableName, AddressName, PartNumber, [L/R], FeederSize, QtyPerBoard, SassyName, RawBoard
FROM " & [FORMS]![DASHBOARD]![Combo0].SelectedValue & ";"
DoCmd.RunSQL (strSQL2)

You may need to tweak that a bit, but it should get you pretty close.

You MAY need to use [FORMS]![DASHBOARD]![Combo0].Columns(0) or Columns(1) instead, I can't remember...

Upvotes: 1

Mark C.
Mark C.

Reputation: 6450

So, the user essentially wants 2 queries to run. A DELETE * FROM Table query, and an Append query. The user wants to know what table to utilize for the Append query by using the Combobox (may just be my assumption/interpretation). That being said, why not use something along the lines of:

If IsNull(Me.[Combo0].Value) Then
    MsgBox "Please select something."
    Me.[Combo0].SetFocus
    Cancel = True
Else
   Select Case Me.Form!Combo0
       Case 1
           DoCmd.OpenQuery "DeleteMaterialsTableData" 'Query to delete appropriate table data dependent on Combobox selection'
           DoCmd.OpenQuery "QueryNameMaterial1" 'Append records to appropriate table dependent on Combo0 selection'
       Case 2
           DoCmd.OpenQuery "DeleteMaterialsTableData" 'Query to delete appropriate table data dependent on Combobox selection'
           DoCmd.OpenQuery "QueryNameMaterial2" 'Append records to appropriate table dependent on Combo0 selection'

This is just trying to use the users' combobox values to determine which table to run the queries against, instead of the user trying to use the Combobox's value as a table name.

Upvotes: 1

Related Questions