Public strOldTable,strNewTable as string
OldTable has data, NewTable is empty but with same field structure.
I assign names of tables to these variables and attempt to fill up the NewTable with the data from the OldTable using :
DoCmd.RunSQL "INSERT INTO ('" & strNewTable & "') SELECT * FROM " & ('" & strOldTable & "')
I can find examples of use of the OldTable variable but not of use of the NewTable in this SQL.
Hi
I have a form that displays line items and their cost. At the bottom of the form I have a text field called text17 that uses a sum to total the value of all the line item costs with this : =Sum([Line Cost])
How would I pass that value into a database table?
I have a normalized DB with one to many relationships, using Primary and Foreign Keys.
I need to do inserts and maintain the PK/FK relationship, which means when I add a new PK I need to insert that PK as FK in other tables in the same transaction.
How do I do this in Access? SQL Server I use transactions, but I can't lock up the tables in Access like that.
I did a bunch of searching and found nothing, which leads me to believe I am way off in my thinking. Below are the dirty details
I have 2 tables, tblName and tblPhone. 1 name can have many phones.
tblName has PKName. tblPhone has PKPhone, FKName.
I have a form where user enters a new Name and PhoneNumber.
Name gets inserted to tblName, assigned with PKName = 100
Phone should get inserted into tblPhone with PKName.
IE Insert into tblPhone (FKName, PhoneNum) VALUES (100,"212-555-1212").
The dumb way I am doing it now is I insert to tblName, query tblname for the PK, then write to tblPhone. This can't be right.
I have a query that has three fields: 1) Days 2) Bit Size 3) Feet. The Days field has a date range specified in the Criteria that comes from two unbound fields on a form. I have a second query based on the first: Two fields are Days with the Total as Min and Max. Three fields are Feet with the Total as Min, Mac, Avg. One field is Bit Size with the Criteria as a specified bit size (i.e. 6.125). These queries are used on a report, and all data is inputted from forms prior to the report.
The queries work as expected with a specified bit size. My problem is that while Bit Size is normally chosen from a list of sizes, a custom size can be inputted. I am unsure how I can specify the Criteria in Bit Size to be a custom size..? To compound things further, more than one custom size can be used.
A) I need a way to pick out the custom sizes from the first query and B) use them as the Criteria for the second query. If I use VBA, I assume I could complete B) by using a WHERE clause (if the custom size(s) has been defined)...
Im trying to pass a list box variable in a select query. I understand you cannot pass a variable directly but have to pass it through a function. I may be wrong in this, but whatever I do I cannot get it to work. Here's my code:-
Public Sub GetEquipment()
List387.RowSourceType = "Table/Query"
List387.RowSource = "SELECT findequipstr() FROM Equipment"
End Sub
Public Function findequipstr() As String
If IsNull(List371.Value) Then GoTo function_end
findequipstr = List371.Value
function_end:
End Function
If I MsgBox(findequipstr()) within my Getequipment function, the variable is messaged,
The following code doesn't work:
SELECT CompanyName.WaferID
FROM CompanyName
Here, the values of [CompanyName] could be A,B,C, which is chosen by in combo box of a form. And, of course, there are three tables: A,B,C.
How to introduce the variable CompanyName in SQL?
I have a query like this
SELECT qryBONUS_pre.PACIENT_ID, Sum(qryBONUS_pred.TOTAL) AS SumOfTOTAL
FROM qryBONUS_pre.TOTAL
GROUP BY qryBONUS_pre.PACIENT_ID;
I like SumOfTOTAL to put into a variable
How to do it
I wan't to create special query who show only special package. It must work that.
1. Client logg in. System rember his chose. varPrawa="Client" and varIDLogin=5
2. Client click at button "Show Your Package". At that moment query haven't got any criteria so it show every package but it must show only package with IDClient=varIDLogin so only package with IDClient=5 it is easy to create. I tell more it is still easy to join other person but only with varPrawa="Client" i want to create one query who must select by.
WHEN varPrawa="Client" then show only package with IDClient=varIDLogin
WHEN varPrawa="Kurier" then show only package with IDKurier=varIDLogin
WHEN varPrawa="Spedytor" then show only package with IDSpedytor=varIDLogin
WHEN varPrawa="Administrator" then show all package without varIDLogin criteria.
I create subraport who use chosen package in my list and display all special data using varaible but my problem is how to create query whose can select and display only package of that Client or Kurier or Spedytor.
At the end
When Client is online he can see only his packages special IDClient (any IDKurier, IDSpedytor)
When Kurier is online he can see only his packages special IDKurier (any IDClient, IDSpedytor)
When Spedytor is online he can see only his packages special IDSpedytor (any IDClient, IDKurier)
When Administrator is on line he can see all packages (any IDClient, IDKurier, IDSpedytor)
How i can create this query?
I am creating a 2 level report to confirm an order. Main report already created, runs successfully called as subform/subreport under "OrderDetails" form. Linked to master using Order.ID. There are two versions of the confirmation report that have different layouts for different program types.
The hangup comes when I try to add a "Class Dates" subreport. It lists dates of individual classes and Skip dates. I have created the subreport as "srClassDates". When I add it to the main report, it lists the records. However, when I try to link it to the Main report, an error message box appears with the "object variable or With block variable not set".
I have tried rebuilding both the main and subreports, rebuilt the query, have not found anything that changes the result.
Linker has been working successfully on other subforms. Report with groupings works fine, but I need data from 2 tables both linked to order.id.
Ok, so now this is where it's getting more complicated... (though I guess this is where you guys usually start)
When you have a query, and you type in your inputbox thingy like so:
http://reenen.fateback.com/image.jpg
PeriodEnd: [Enter period end date dd/mm/yyyy: ]
And you can have multiple, then it will prompt you for several of them.
Now this is not very tidy, so I'd rather do it on a form. How can I display the form as the query/report runs, and insert the values entered into the form as the inputs into my variables? (This case PeriodEnd)
-Canderel
Grrr... I can't get the image to display.
What would the best way to open a series of queries, if you aren't sure how many of those queries there are? I have the following:
Code:
DoCmd.Close acReport, "SCEFReport"
DoCmd.OpenQuery "ClearTBLQuestions", acViewNormal, acEdit
DoCmd.OpenQuery "Q1append", acViewNormal, acEdit
DoCmd.OpenQuery "Q2append", acViewNormal, acEdit
DoCmd.OpenQuery "Q3append", acViewNormal, acEdit
Which continues as is for 10 or 11 queries named in a similar pattern, then prints a report. However, as the number of append queries is not set (they are generated in a different quantity for different purposes, it's complicated), I am not sure how to check how many there are with that style of name.
is it possible to name a field in a query or a table with a variable name?
For example:
I want to choose in listbox "fieldname" the name of the field.
And the query field name looks like:
Code:
forms![testform]![fieldname]: FieldOfTheTable
Unfortunately it doesn't work.
Also if I save the choosen fieldname in a vba function.
Code:
fieldNameFunction(): FieldOfTheTable
This doesn't work, too.
How could i get output of a Query in a VBA variable ?? I'm trying as below butIi'm getting error:
Dim output As Integer
vari = DoCmd.OpenQuery("qryCompare", acViewNormal, acReadOnly)
Output of a query is a single MAX value.
I am trying to insert a new record directly into a table using INSERT INTO sql. The field data is coming from some text boxes on an unbound form.
I keep getting an error telling me "too few parameters. Expected 5"
This is my code
Dim db As DAO.Database
Set db = DBEngine(0)(0)
Text7.SetFocus
pJobID = Text7
Text1.SetFocus
pFitter = Text1
[code]....
This works if i substitute the line:
& "(pJobID,pFitter,pNetValue,pInvNotes,pBookingRef); "
With specific test data (ie i put specific values in the VALUES section of the SQL). but not when I am using variables.
I am trying to use a criteria to filter out nulls in concatenated variable, which slows the query considerably.The part that makes it slow looks like this -
WHERE ... AND (([tbl1].[x] & [tbl1].[y]) <> "") AND ...
If I work them individually, it's not a problem, the query is fast, but I have to combine them first in order to produce the correct output.
I am trying to copy the parameter value that is entered to a variable so that it can be used to run other queries without typing it in over and over. Once my form is finished it will run 4 queries, and the current setup is that I would have to retype the parameter each time.
View 9 Replies View RelatedI use access 2010. I have a form that allows the user to assign products to operations. Each operation can produce multiple products and each product can be produced at multiple operations. I manage this many-to-many relationship through the use of a third table that stores primary keys for associated products / operations.
tblOperation
tblProducts
tblOperationProducts
The query im trying to design will populate a listbox on the form that contains products which are available to be added to a given operation. In addition to the listbox there is also a combobox that allows the user to select the operation they want to add products to. As the user selects an operation, the query behind the listbox should return records for all products except the ones that have already been assigned to the operation selected in the combobox.
So far I've been able to make the listbox return all products always or return products that haven't been assigned to any operation at all, but I'm struggling to formulate the logic in my mind on how to show only products that have no association (through the tblOperationProducts table) with the operation selected in the forms combobox.
This is the query I have right now, it's not working:
Code:
Select tblProducts.ProductID
From tblProducts
EXCEPT
SELECT tblProducts.ProductID
FROM tblOperation RIGHT JOIN (tblProducts RIGHT JOIN tblOperationProductMM ON tblProducts.ProductID = tblOperationProductMM.ProductIDMM) ON tblOperation.OperationID = tblOperationProductMM.OperationIDMM
WHERE (((tblOperation.OperationID)=[Forms]![frmOperationProducts]![cboOperation]));
Error 91 - Object variable or With block variable not set
I am getting this error telling me that an object variable is not set.
I know which variable it is but when I step through the debugger it sets the variable and all is fine? Issue is that public variable of a class is not getting set when the VBA Editor is not open?
This code runs fine the FIRST time, however trows up a message the SECOND time it is run.
The error is on the line ".Range"
I am trying to sort records which have been exported to Excel.
Dim LR As Integer
LR = 5
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set wbRef = xlApp.Workbooks.Add
With wbRef
wbRef.Activate
.Worksheets("Sheet1").Activate
With ActiveSheet
.Range("A2", .Cells(LR, "O").End(xlUp)).Sort Key1:=.Range("C2"), Order1:=xlAscending, Header:=xlYes
End With
end With
I am trying to write a query from a main table which will show records which have a date of less than or equal to a Variable (tempvar), however the variable (selected from a form) may be left blank in which case all records should be shown.
I have successfully used the following but the records returned are only the specific date choosen not that date and all prior
Like Nz(([TempVars]![tvcldate]),"*")
So I have tried the following but it doesn't work.
Like Nz(<=([TempVars]![tvcldate]),"*")
I look at a lot of files to see when they were last updated. I wanted to write a generic procedure to manage that so ..
Code:
Public fDate As Variant
Public vField As String
Public vFile As String
'GTSdata
vField = "txt_gts_data"
[Code] ....
What I hoped Me.vField would do is update the date field [txt_gts_data] on my form with the date the file was last saved.
i.e. me. txt_gts_data = fDate
What actually happens is the variable vfield gets updated from "txt_gts_data" to 19/08/2014 then later code falls over because the fieldname is lost .
Me.[vField] corrects itself to me.vField (and does not work)
Me!vfield falls over (cannot find the field vField, not surprising J)
How do I say update the contents of the variable, not the variable itself?
Runtime error '91'
Running Access 2010.
I have two reports running off of the same crosstab query. I copied one report to make the second report, then modified the second report to change the background of column fields satisfying certain conditions. These lines of code were added to the Detail_Format section, in color below. The report with the extra code lines does not error--the original report errors.
Code:
Option Compare Database
' Constant for maximum number of columns EmployeeSales query would
' create plus 1 for a Totals column. Here, you have 9 employees.
Const conTotalColumns = 11
[Code] .....
If I say OK (rather than debug) after the error message, I can then click the button for the report again and it runs without complaint. And, as I said, the report with the added code never errors.
Is it possible to select some data in queries in the same db to a table?
Many thanks!
How to insert x number of empty rows at once in a query? All fields allow null.
Say, if I wanted to "resize" the table to 3000 records...
This code:
Code:
Private Sub txtPart_LostFocus()
Dim db As Database
Dim strSQL As String
[Code]....
But no entries are added to the table.
I open a query in design mode, paste the first debug above in SQL VIEW and run it:
Code:
INSERT INTO tblPartSpecs (PartID, SpecID, Sequence) VALUES (19, 14, 1);
And the entry is added in the table.
PartID and SpecID are long integer, Sequence is integer.
I originally had Me.txtKey in the SQL, but I saw a comment in a post about that being a possible culprit. So I copied it into a long integer, still didn't work (latest trial above).
I have 2 table and the relation between the 2 table are [Booking_Num]
Code:
table A: [ID],[Booking_Num] [Prod_Name],[Qty_Order]
table B: [ID],[Booking_Num],[Prod_Name],[Date_Out]
when I get insert to table A, should be the table B get many record base on [Qty_Order]..?
sample:
Code:
table A
ID | Booking_Num | Prod_Name | Qty_Order |
01 | BOK001 | Hammer | 4 |
table B
ID | Booking_Num | Prod_Name | Date_Out |
01 | BOK001 | Hammer | Null |
02 | BOK001 | Hammer | Null |
03 | BOK001 | Hammer | Null |
04 | BOK001 | Hammer | Null |