Geek asks and answers

Link to the form field in a query using SQL

I have an Access 2007 database that will contain tables that relate to the bill of materials for several products. In the main form, I want the user to be able to choose one of the products - good, easy. Now I want the two queries to be launched as soon as they click the button after selecting their product from the drop-down list. The first query is a simple delete request to remove all information from the table. The second query is where I had a problem with SQL syntax. I want the information from the static table to be added to the table where the delete request just deleted everything.

Now each table in which the bill of material for each product is stored is marked with the name of the product. So I want a dropdown list ( combo0) to be the reference point for the table name in FROMn in the SQL string. The 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 am trying to refer to the product name in the form drop-down as the table name. Please let me know if possible.

+3
source share
5 answers

"Please let me know if this is possible."

This is not possible in Access SQL.

The db mechanism can only accept the actual table name --- it cannot reference the form control to find the table name and not accept any other type of parameter to get the table name.

, combo , SQL .

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

Access. .

+1

, 2 . A DELETE * FROM Table Append. , Append, Combobox ( , /). , - :

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'

combobox , , , Combobox .

+1

, . , VBA ( Click ). "".

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)

, , .

[FORMS]! [DASHBOARD]! [Combo0]. (0) (1) ...

0

; ( ) .

. : FROM [FORMS]! [DASHBOARD]! [Combo0];

(), .

- Select, , . . .

- Append, Select .

Append ( Select ), vba : Docmd.OpenQuery " "

0

100% MS Access 2010, . 2007, MS , (. ). .

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

, , - , , Date "DateTime" . Microsoft, , , 2007 .

Use parameters in MS Access queries

In addition, if you want to do a deletion or addition, save it as a request, put the button on the form that executes docmd.runquery for the name of this saved delete / add request.

-1
source

More articles:


All Articles