Variable Table Name? Need Some Urgent Help!
Apr 1, 2007
hey is it possible to have a variable table name;
this is my scenario:
i have four category of products, which i am selecting using a pull down menu. depending upon which the user chooses, a particular table of database shud be searched.
this is what i have been tryn:
tablename=CStr(Request.Form("category"))
strq="Select * from '&tablename&' where (model='" & CStr(Request.Form("model")) & "') ;"
but im getn the error: incomplete query.
been tryn double and single quotes. just not getn it.
please help! have to submit my project morrow!!!!!
View Replies
ADVERTISEMENT
Aug 22, 2006
I have a large problem that i need to get figured out ASAP! I have a form text box (named actual cost) that needs to pull a specific record from a table
Now in this table it has multiple rows according to each job. There is a text box (Project number) that has the project that this (actual cost) box should pull from the specific row in the table.
if anyone can help me it would be the biggest help.
-Thanks
Jon
View 3 Replies
View Related
Feb 28, 2008
i have 2 mdb and both mdb have table link
in one of the mdb , they had a link to dbf (paradox) and they have lot of queries and reports created.
Now the functionality has changed so we have created a access table and need to replace the dbf with the table.
The problem is we need to retain all the queries and reports as such but the table will be access table instead of dbf.
Example
First.mdb has test table
Second.mdb has a table called old.dbf. and associated quries
but now test table is been linked to second.mdb and we got to rename or delete old.dbf and associated all queries and reports pointing to test table
is there a way to do it automatically or should it be done manually?
Please let me know as it is urgent
View 1 Replies
View Related
Jan 21, 2005
Can anyone please tell me the maximum number of records a table can accomodate? Also what happens if the number of records exceeds this maximim value? Awaiting reply
View 2 Replies
View Related
Aug 6, 2007
Hi all, i need some help constructing a query that will create a table for me that does not include the Type value of 'promotion' if the same ID value exists elsewhere within the query.For instance, in the example below, i would want to delete the first entry containing 'Promotion' because ID2001 exists in two other places. However, if only one instance of ID2001 'Promotion' existed in my query, i would want to keep that entry. I would prefer to write all my data to a new table called "Test", This query has to sort out about 1000 entries when executed, otherwise i'd do it manually.I'm pulling my hair out with this one, so could someone help me?ID Company Name TypeID2001 Disc plc John PromotionID2001 Disc plc Paul AgentID2001 Disc plc George Agent
View 3 Replies
View Related
Apr 16, 2013
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.
View 2 Replies
View Related
Jul 8, 2013
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?
View 14 Replies
View Related
Oct 3, 2014
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
View 3 Replies
View Related
Apr 7, 2005
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?
View 14 Replies
View Related
Aug 28, 2004
Hy,i have this problem:
I've got a table that has column called car_id which is primary key and autonumber.
How can i get last record from that column and assign it to variable which i defined as integer.
Is there something like:
myvariable=max("car_id","mytable") ????
Thankls for any help
View 2 Replies
View Related
Jan 16, 2013
I'm using Access 2010 and am trying to pass a variable in a form field and store it in a table i.e. I have a customer database with various fields: Customer_ID, Customer_Name, Customer_Address etc. After selecting a particular record i.e. Customer_ID = 20 l need to store the record number '20' in a temporary table. I presume that l could then use Dlookup to read the info back at a later stage.
View 7 Replies
View Related
Aug 20, 2014
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?
View 7 Replies
View Related
Apr 15, 2015
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.
View 4 Replies
View Related
Jul 19, 2014
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.
View 14 Replies
View Related
Aug 14, 2014
I'm trying to run this very very simple code but I get a Run-time error '3450' Syntax error in query. Incomplete query clause. For sure the problem is the variable I used as Table parameter but I cannot understand where my error is :
Private Sub cmdRunCheck_Click()
Dim strSQL As String
Dim strTempTbl As String
strTempTbl = "tblCheckDoubles"
strSQL = "DELETE * FROM " & "'" & strTempTbl & "'"
CurrentDb.Execute strSQL, dbFailOnError
End Sub
What is clear is that if I use this line the Sub is working smoothly
strSQL = "DELETE * FROM tblCheckDoubles"
Please do not tell me that it is not possible to use a variable as Table parameter....
View 8 Replies
View Related
Jun 26, 2015
I am trying to deny users from logging on from multiple pc's. Here is how i've got it so far,created table tblLoginLock with fields EmpID and LoggedIn both numberical data type created a form to set the Empid with tempvar!TempEmpId and LoggedIn as 1 when a user logs in.When he clicks the logOff button he is signed out and a form opens up and closes to set the LoggedIn field to 0.Now whenever a user is signed the LoggedIn Field is checked with this expression
Code:
DLookUp("[TempVars]!TempEmpID","tblLoginLock","[LoggedIn]=1")
if there is a one then the user is logged in the condtion is met and a message box pops up to warn the user that he is already logged in.
The above code does not check the specific TempVar to the LoggedIn field, the LoggedIn field is randomly checked for a '1' meaning if there is a '1' anywhere in the table then the condition is met and the user denied. how can i set the condition to validate the logged in user to the LoggedIn field.
View 3 Replies
View Related
Aug 28, 2007
I am trying to create a table to show the parent/child relationships between assemblies, sub-assemblies and detail parts. The table I start with has 2 fields a "Part Number" and an "Indenture".
Please forgive all the periods, couldnt think of how else to seperate the values to depict the tables.
Example:
IND..PN
00....A
01....B
01....C
02....D
03....E
03....F
01....G
00....H
01....I
02....J
I can get the first step to completeing the new table of parent/child relationship like this:
PN....00....01....02....03
.A.....A
.B....."".....B
C......"".....C
D......"".....""......D
E......"".....""......"".....E
F......"".....""......"".....F
G......"".....""......""....G
H......H
I......""......I
J......"".....""......J
Where "" are null values
My problem is making it so that every part number has its own array, aka [family tree], so that for part "F" the table would hold "A,C,D,F"
I though an update query where for example:
*"Zero" is a single field string.
IIF([00]<>null(), Save [00] to Zero, Write Zero to [00])
Would work but have been unable to find any information about passing a field value to a variable for later use or comparison. I would have already done this in excel if the table had less that 65000 records.
Example of final table:
PN....00....01....02....03
.A.....A
.B.....A.....B
C......A.....C
D......A.....C......D
E......A.....C......D.....E
F......A.....C......D.....F
G......A.....C......D....G
H......H
I.......H......I
J.......H......I......J
Any help is greatly appreciated.
BsK
View 1 Replies
View Related
Jan 5, 2005
This is the first time that I have done any major work with forms. After I thought I had finished a problem came up. There are several forms that are use to input information into a table. When the form is opened it grabs an automated number for tracking. The problem is, that if the form is opened and then closed it creates a line of data with all null values.
What I would like to do is have the form open, the user fills out the information, and upon pressing the "save" button, the data will save to the table. I think I need to have each text box write to a variable and then save on the click event. Or I could be completely wrong and need to do something else.
i'm guessing this is something I would learn in Forms 101 if i had ever taken that type of course
Thanks
View 7 Replies
View Related
Jun 14, 2013
I'm trying to search for a variable (varCod) in a table(SerialNumberCustomer) but I want to only store the returned variable in an array if its shipDate is between 9/30/2001 and 10/1/2012
this is what my code looks like so far:
Set rst = CurrentDb.OpenRecordset( _
"Select * from SerialNumberCustomer WHERE SerialCardId = " & varCod & " AND (ShipDate BETWEEN #09/30/2001# AND #10/01/2012#) ")
The line of code works and it returns all of the values in SerialCardId where it's = to varCode but it doesn't go through with the shipDate requirement. It's instead returning all of the dates with the found value.
View 13 Replies
View Related
Apr 22, 2012
Im trying to create a loop that searches through a table and increments a variable every time it finds a record with a specific field set to a specific value. Below is the code but its not working. I then use the variable for another field.This is used in a form btw.
Dim counter As Integer
counter = 0
Set rst = CurrentDb.OpenRecordset("TableName")
Do Until rst.EOF
If rst(10) = "True" Then
counter = counter + 1
End If
rst.MoveNext
Loop
Me.Text24 = counter
View 12 Replies
View Related
Mar 14, 2014
I wanna extract a variable count of columns and transpose it to another table.
My source table is called FC containing columns like 1 FC, 2 FC .., 12 FC
My target table is called Forecast_Quantities
Tried the following:
Code:
Public Sub TRANSPOSE()
Dim rs As DAO.Recordset
Dim rsNew As DAO.Recordset
Dim varItm As Variant
Dim I As Integer
Set rs = CurrentDb.OpenRecordset("FC")
[Code] ....
Somehow it doesn't recognize the I FC column in the table FC.
View 1 Replies
View Related
Nov 19, 2014
I wanna create a button in form which can allow me to import my data base file with some tables , i would select just the variable that i need in the table not all of them .
View 2 Replies
View Related
Feb 7, 2014
I'm quite new to Access and am just working on a little practice database where I keep track of sales of a product.
Basically I have a table keeping track of the different types of products, a table keeping track of customers, a table for orders and one for replenishments of stock. I have a query that calculates the stock of each type of product based on replenishments and sales.
Now I want to make sure that a customer can't order any more than there is in stock. I know you can restrict what's entered into a table with a validation rule but that's only for static restrictions. After searching the internet for an answer I've pretty much learned that I can't really make this restriction directly in the table, correct? But then how can I achieve this? Do I have to restrict the amount through a form somehow?
View 3 Replies
View Related
May 31, 2014
The VBA code I have at the moment:
me.Results.Rowsource = "SELECT car, title, FROM dbo_inventory"
Is it possible to add a variable to a specific row in listbox using the code above? In this case the price?
View 3 Replies
View Related
Apr 23, 2006
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
View 1 Replies
View Related
Dec 14, 2006
Good afternoon all,
The following block of code was working great until I came into work this morning. This routine is called from the OnClick event of several combo boxes. When it does, I recieve the error listed above (Object variable or With block variable not set) on line:
If Me.cbxAss_Filter <> "All" Then
Any ideas? I'm running on no sleep in the last 24 hours so I'm sure I'm missing something simple
The code itself checks the contents of a form and builds a string of conditions which I apply to the Form's filter property to filter records.
Code:Public Sub CreateFilter() '************************************************* **** 'Name: CreateFilter 'Purpose: Generate a string to filter the form 'Inputs: None 'Outputs: None 'Instigates: Me.Filter ' 'Updated: 11/30/06 'By: Chris Lounsbury '************************************************* **** 'Vars Dim strFilter Dim lngLength As Long strFilter = Null strFilter = "" 'Each filter box has its own check for contents If Me.cbxAss_Filter <> "All" Then strFilter = "assigned = '" & Me.cbxAss_Filter & "' AND " End If If Me.cbxAction_filter <> "All" Then strFilter = strFilter & "action = '" & Me.cbxAction_filter & "' AND " End If If Me.cbxStatus_filter <> "All" Then strFilter = strFilter & "status_rsrch = '" & Me.cbxStatus_filter & "' AND " End If If Me.Combo34 <> "All" Then strFilter = strFilter & "rims_flags = '" & Me.Combo34 & "' AND " End If If Me.cbxAAMB_filter <> "All" Then strFilter = strFilter & "aamb = '" & Me.cbxAAMB_filter & "' AND " End If 'Check if filter string was built If strFilter = "" Then Me.FilterOn = False Else 'Determine length of Filter String 'minus the trailing ' AND' lngLength = Len(strFilter) - 5 'Chop off ending ' AND' and set the form filter If lngLength <= 0 Then Else Me.Filter = Left(strFilter, lngLength) Me.FilterOn = True End If End If 'debug 'MsgBox (strFilter) End Sub
View 1 Replies
View Related
