Hi there - on one of my forms i have a text box where the user can write the contents of a letter. When a button is pressed, a report is run, and the text from the form is entered into the report -
this all works fine - but when the text reaches a certain length, it goes all unreadable. SO i assume this use of a temp variable (as its not being stored in a table) comes with a text limit? Is there anyway to extend this?
Cheers
I need to extract a specific number of records into a table using a MakeTable or Append command using a temp variable, e.g. TempK&SA. Previously on the forum I was shown how code could be added to the OnOpen function to use a temp variable to select a specific number of records to report. ACCESS does not have the OnOpen function in the design view of a query like in the report. It does allow a SELECT TOP but only with fixed variables or percents (e.g. 25 in the code below).
The beginning code for the make table query (where 25 is the number of records added) is:
INSERT INTO [Output] ( RndNo, PointBiserial, BloomsTax, DateRevised, Exam1, Status, Exam2, Exam3, Exam4, [NCCPAKnowledge&Skills] )
SELECT TOP 25 TestBank.RndNo, TestBank.PointBiserial, TestBank.BloomsTax, TestBank.DateRevised, TestBank.Exam1, TestBank.Status, TestBank.Exam2, TestBank.Exam3, TestBank.Exam4, TestBank.[NCCPAKnowledge&Skills], *
FROM TestBank
WHERE (((TestBank.PointBiserial) Is Null Or (TestBank.PointBiserial) Between [TempVars]![TempPointBiserialLow] And .....
how to modify the code to allow a temp variable to determine the number of records to append to another table would be gratefully received. (This process then is repeated for a total of 7 append tables with different temp variables.)
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.
Hey all, I was thiking can i make a blank query and then on my filtered form. Pass the data from the selected fields(VIA a ceckbox) and then display a report based on that query? Then have the query cleared for the next time?? Is this possible? Thanks in advance for any help!
View 14 Replies View RelatedError 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
Is it possible to have a form that will filter my data to what i need, and place in a temperary table and then be able to display it in a report??? So I already have a filtered form, however I would like to be able to creat reports on the fly. So I will not need all my fields from my table everytime I filter. So If I create a report I can choose the fields that I need, however this is done will all records in my table and I would like to only use the data I have filters. What way would be best to accomplish this?? I already have the ability to load the report wizard by a command button. I just dont know the best way to use my filtered data with it. Any help would be great. thanks
View 4 Replies View RelatedI have just been working with some temporary recordsets in access 2000.
After working with the recrodsets each is closed and set to nothing but this leads to bloating of around 20Mb on a 70Mb backend database.
So I (in messing around) I added "DoEvents" after closing each temporary recordset and the bloating reduced to just 8Kb.
Is it normal practice to force the closure of temporary recordsets before moving to the next step using DoEvents, because this certainly seems to be suggested by this result?
Vince
Hellooo
Hi gurus
I have a smallish problem
I have two tables that I need to join togther - normally no problem
I have one table with 1 event on it- easy
however if I have more than 1 event on it I have another table that opens up and I add multiple evnets to it
main id number 12345 with 1 event on it and
23456 may have 20 events on it
on my other table (with multiples on it) i have this autonumbered (this is great unique id - now i need to make a temp table to include boths sets of data in one file
1 event table - easy
multi event tabel I want it to get the main refernce fromt he first table (using 23456 as the example) and have it list these in the table - main refer 23456-multi table unique ref number 23456-1 , 23456-2
so my table should have
12345
23456-1
23456-2
23456-etc
I have been using append qry to make tables - any pointers would be great
GP
Here's the scenario:
I create a temp table structure, which works fine.
I create a recordset from SQL pulling data from a DB2 connection. This works fine.
I want to put the entire recordset result into the temp table.
I have a working means of doing this, but it is very inefficient and leaves the user staring at an hourglass for a minute or two.
What I am currently doing is iterating through each record of the recordset and appending it to the temp table. Desired results, yes, but takes way too much time.
Is there any way to simply 'dump' an entire dataset into the table, instead of on a record-by-record basis?
Here's some code from how I am currently doing things:
Set cmdP1 = New ADODB.Command
Set cmdP1.ActiveConnection = cnnP1
cmdP1.CommandText = "SELECT DISTINCT distribution_id FROM " & sDB & " ORDER BY distribution_id"
Set rstP1 = cmdP1.Execute
Do Until rstP1.EOF
With rst_Temp
.AddNew
.Fields!distribution_id = rstP1!distribution_id
.Update
End With
rstP1.MoveNext
Loop
Any help will be most appreciated! Thanks!
John
Hey all-
I'm trying to create a simple 1 field temp table to populate a combo box with the name of the current user and the word "Company." However, after the user closes the form (or as soon as the Temp table is no longer necessary) I would like to delete the table. I can create the table, the fields, add the data, and populate the combo box just fine, but I'm having problems deleting the table after I'm done. I keep getting the error:
Run-Time Error 3211: The database engine could not lock table 'Temp' because it is already in use by another person or process.
here's my code:
Code:Option Compare DatabaseDim dbRoofing As DAO.Database Private Sub Form_Close()dbRoofing.TableDefs.Delete "Temp" 'where i get caught when i close the formEnd Sub Private Sub Form_Open(Cancel As Integer)Set dbRoofing = CurrentDb Dim tblTemp As TableDefDim rcdTemp As DAO.Recordset Set tblTemp = dbRoofing.CreateTableDef("Temp")tblTemp.Fields.Append tblTemp.CreateField("Owner", dbText)dbRoofing.TableDefs.Append tblTemp Set rcdTemp = dbRoofing.OpenRecordset("Temp", dbOpenDynaset)With rcdTemp.AddNew!Owner = CurrentUser.Update.AddNew!Owner = "Company".Update.CloseEnd WithOwner.RowSource = "SELECT Temp.Owner FROM Temp"End Sub
thanks guys
Hi all,
I have a form with around 10 checkboxes which serve as a filter option...now, when I hit my cmdFilter button it works well with a simple MsgBox !Ime showing all the filtered names...now, I want to put the results into a table tblTemp so that I could show the results in my subform. I've tried with making a sql string something like "INSERT INTO tblTemp..." but it's still empty.:confused:
Since this table will serve as a one time data, I will need to delete all records when I hit the Filter button next time...so, how do I send my recordset data into my table.
I hope this sounds understandable...
Thanks a lot,
Daniel
I have a table [Employees] which has the following Fields
- TxtEmpID
- StrEmpName
- StrEmpPassword
Which I use for my login table with the following script:
If Me.txtPassword.Value = DLookup("strEmpPassword", "tblEmployees", "[TxtEmpID]=" & Me.cboEmployee.Value) Then
TxtMyEmpID = Me.cboEmployee.Value
On my form the drop down to select the employee is
SELECT tblEmployees.[TxtEmpID], [tblEmployees].[strEmpName] FROM tblEmployees;
(Seemingly all this is based on the TxtEmpID)
When I create a Temporary Variable
[SetTempVar screen.activecontrol with the name SetUserID]
And then want to place the variable in a form or whatever else, I can only see the TxtEmpID. I would like to set the TempVar as the field StrEmpName.
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.
I'd like to copy data from an excel spreadsheet and paste it into a temp table in Access and then hit a button which will run an append query and append all the data in the temp table to a permenant table.ow to create a temp table?
View 3 Replies View RelatedI am trying to create a table from a form. The form has several fields but I need to take the value from 4 separate combo boxes ([cr] +[br] +[tr] and add them, then add the value from one more combo box [inc] to be my beginning value in a table.
I then need to add the last value [inc] to the total and that become the next line in the table. I would the like to add this value [inc] an infinite number of times until it reaches a max number.
The scenario would be something like this
cr=3 br=2 tr=3 inc=1.5
So the first total would be 9.5. Then every row after that would be plus 1.5
11
12.5
14
15.5
17
and so on.
This would be a temp table that I would run a query on to let an operator know lengths they can choose from in a combo box. I don't know if this is even possible.
All using access 2010. I have a multiuser database that I feel would benefit from splitting into a backend with multiple user frontends. My problem is that there are tables from make table queries processed every two weeks that all users need access to. As far as I know; you can not put a table in the backend that you will delete and remake or a temporary table and link to it in the front end. Is there any other way I would be able to split the database and have temporary tables linked from backend to frontend that I am not aware of? Is there another way to creating temporary tables and tables from make queries? This database has 9 users and counting and really needs to be split. r
View 14 Replies View RelatedI have a form where the user selects check boxes to choose what fields to include in a report. Because of the massive amount of data, I need to send it to a temp table and then on to Excel and not use a query.
I have this line of code, for the insert into temptables for other forms that don't require the yes/no box and it works very well, but I can't figure out how to do it with these yes/no conditions.
This is the execute line that inserts into the TempTable for the other forms:
db.Execute "INSERT INTO TempPicktbl (Field1, Field2, Field3, Field4, Field5)" & strQuery, dbFailOnError
How would I write:
If Check1=True Then INSERT INTO TempPicktbl Field1
If Check 2=True Then INSERT INTO TempPicktbl Field2
If Check 3=True Then INSERT INTO TempPicktbl Field 3
etc.
I'm pretty sure it's the INSERT level where I need to put this code.
I am attempting to automate the import of a .csv file from a given directory and copy and append it to another table in a different layout. Below is my code. The error that i'm getting is it can't fine the copyobject in the database...
Option Compare Database
Option Explicit
Function DoImportandAppend()
Dim strPathFile As String
Dim strFile As String
Dim strPath As String
[Code] ....
I have some code that creates a table based off another table. This code works perfect but Im trying to alter my ID field and change it to an auto increment. How can I fix my code so that it alters my ID field?
Code:
Private Sub Command0_Click()
'OBJECTIVE: to build a table by extracting some fields from a main database
Dim rst As Recordset
Dim strSQL As String
Dim strSQL2 As String
Dim intCount As Integer
[Code] ....
I am trying to export into a temp table (all text fields because it will be going into a text export later) and I'm having difficulty adding 0:00:00 onto the value of "ApptdateLast" for the update...
INSERT INTO cbt_Export_Temp ( TransactionType, ID, ApptdateLast )
SELECT "Add" AS TransactionType, "BC" & [TransId] AS ID, dbo_Info.ApptdateLast & " 0:00:00" AS ApptdateLast
FROM dbo_examInfo
I have a MainTable with Subform. The Subform is based on a Query. I want to save the data/Fields from subform to my TempTable..
MainTable Fields:
EmployeeNo
Name
Position
TempTable Fields:
ID*
Salary
Allowance
Subform Fields:
EmployeeNO (not Shown on Query results)
Salary
Allowance
I want the fields in my subform to be saved in TempTable..
I am trying to write a VBA Module that will look in a directory for Excel files with a specific file prefix, and then import specified cells into a temp table. Once it processes the first file, it copies the file to another folder, and then kills the file. However, when I run my current code, it goes through the loop the first time, but fails on the second attempt, because it is still looking for the first file it came across even though it has been moved. The code is pasted below:
Sub Count()
Dim xlwrksht As Excel.Worksheet
Dim xlWrkBk As Excel.Workbook
Dim nIndex As Integer
Dim strMvPath As String
Dim mvPath As String
Dim strFile As String
[Code] ....
I am attempting to insert a record with selected data into a temp table and I am getting "Run-time error '3075': Syntax error in (comma)...". Here is the code:
Code:
Private Sub XferDataToTempTable()
Dim db As Database
Dim strSQL As String
Set db = CurrentDb
[code]...
I have a networked database. It is accessed with computers that have both Office 2002, and office 2003. I get the following error message on computers with Office 2003:
"Object variable or With block variable not set"
It happens occasionally and the problem generally fixes itself so I dont think there is anything wrong with my coding.
And when it happens on the computers with office 2003 the ones with office 2002 can open the database fine.
What can be causing this?
Thank you in advance