Hi
In a form event script, I need to select a variable from a table into a local (or host) variable. I cannot seem to find how to do this.
I have tried:
strSQL = "Select count(*) into intMyVar from TableA where FieldA = 3;"
DoCmd.RunSQL(strSQL)
but all I could get was zero in my variable instead of a record count.
This is very common in Powerbuilder and Sybase. What is the trick in Access?
Thanks
EzRider
Heya al, ive got a problem with my select max statement. What i want to do is select the maxium value from my table- howver its not returning the maximum value- Instead its returns a middle value e.g. the number 1-50 in my column in my db. I want 50 to appear but it only displays the number 10???
Heres my sql statement- pls i need some advice or code
Code:sub sqlCmd( )'==SQL STATMENT========================================== =================================set objrs2 = server.createobject ("ADODB.recordset")dim sqlStatmentsqlStatment = sqlStatment + "SELECT MAX (Area_Code) AS AreaC FROM [Areas]" objRs2.OPEN sqlStatment,objConn,1,3'================================================= ========================================end sub
I have a combo box which I pull values from a table which has 2 columns. When I pull down the menu, both columns show but I want the 2nd column to fill out the field. Not the 1st column (which it is doing by default)
How do I fix this? Thanks.
Hi All,
What is the easist way to select a column's value in a subform. After the record is selected I would like to write a value ("0") into this column by way of a button click..
Thanks in advance..
I have a form that is a datasheet. it contains multiple records. One of the fields in that datasheet is a combo box. I would like to make it so when I select a value from the combo box it changes all the values of the other records in that column/field to what was chosen from that combo box. is that possible?
View 4 Replies View RelatedI need to get Max(column1) from a table and store in a variable temporarily.
How can I do that without having to create a control(TextBox) ?
Thanks
Bosch
how can I use the column repmonth in the following statement?
repmonth is as a string declared and given by the user, in my case repmonth = 112013.
Code:
Public Sub TEST2()
DoCmd.RunSQL "SELECT PPC_REPORT.Reporting_Month, Report_Products.Division,
Report_Products.Product_Class, PPC_REPORT.Sales_Quantity,
Sales_QUANTITY_BP_2013.'" & repmonth & "' , Sales_QUANTITY_BP_2013.Total,
Sales_Quantity_'" & CF_PERIOD & "'.' & repmonth & "', Sales_Quantity_'" & CF_PERIOD
[Code] ......
i would like to sum all the column fields in a tables column where an id is the same as the id in that table.
I know you can use the SUM function in the select statement of your query.
But how can i set this sum result into a variable, so i can use it for calculation further on in the program?
I work with Access 2010
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.
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 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.
Ladies and Gentlemen,
I'm sure you gurus have an answer for me.
I have a MDB (MS Access 97 db) on a shared drive. All my data resides here.
I have a local MDB (MS Access XP db) on my hard disk. Currently, I have queries located on the shared MDB that puts out a table. I then execute queries on my local DB, linking to the table I have on the shared db.
My requirement is this.
1. I want to be able to run queries against the shared db from the local db.
2. When the create table query is run, I would like it to create the table in the local db. I can live with creating the table in the shared db, but local would be better.
I'm sure that this can be done. I've seen it. But, that has always been a client/server system.
Thank you.
i have done a database for a library that enables the user to enter the information about the books and search for it, the administrator have the right to enter the info about the books and edit and search the others have more limited rights depending on which group they belong, i used for the connection (currentProject) because i did all the forms and tables in one DB
a friend from this forum told me and i think he s right, that if i want to put it on the network i have to separate the forms and the tables ie save the tables and the queries on another database that i put on the network
i have a problem
1- im not sure how to connect to another database can i do it like this
Dim conDB as ADODB.connection
set conDB=new ADODB.connection
conDB=openDatabase("the path") or not
2-i have done the result form with a record source (search-query) and then inserted the fields into the form in the design view
can i do that with a query if it was from another database or i need to write it all in code (taking into consideration that i havent found how to insert datacontrols and do the binding, because im using french access, that s why i assigned automatically a record source for the form)
thanks in advance
Hello,
I've searched the web for almost 2 days...and i'm completely lost:
I've got a simple online DB which stores information from a webform (e.g. name, adress, order, etc). Let's say it's called: www.mywebsite.com/onlineDB.mdb
Is it possible to link the online DB to my local DB (e.g. c:localDB.mdb), without downloading it manually?
Or do I have to put the webform information in a MySQL DB and link that one to my online DB?
TIA,
Leon
I have a database on my laptop that normally accesses a backend on a local server. However when the laptop isn't on that network I would like the database to switch to a local backend on the harddrive. What is the best way of doing this?
I was thinking on startup a simple check to see if the network file exists and if not use the local one but I don't know if this is the best way of doing it.
Hello,
I have a database that the table is located on a sharepoint website. This worked out well in the beginning but now that the database has thousands of records, it has slowed up a bit. What I want to do is at the load of the the database, it will automatically copy Sharepoint table to a local table located on the computer to speed up querys and such.
I was thinking an append query, am I right?
Mateo
Hi all!
I have a bit of an issue. Im trying to retrieve a row from SQL and insert it into my local DB table.
However, when I try and execute the code, I get an error message. The data had not been inserted into my local DB table.
Here is the code:
strsql = "INSERT INTO EntryDetail_Local SELECT EntryDetails.* FROM EntryDetails where entrykeyid = " & plngentrykeyid & ";"
Set qdftemp = mydb.QueryDefs("qry_Append_EntryDetail_Local")
qdftemp.SQL = strsql
qdftemp.Close
qdftemp.Execute
DoEvents
Any ideas?
Cheers
I have a form based on a query where I want to document the status of certain records. The original data is on a SQL server so I am linking to it in my query. I have a local table with the added fields to document my review and status. My query has both tables in it and are linked by the report number. So I have join properties to show all records from the SQL database and only the records in the local table that match. So initially the data in my local table will be blank. I want to use the form to add comments on the status. The problem is, when I type in the form fields nothing happens. It's like the query is confused and won't let the form write back to the local table.
So my question is, do I have my query set up properly so that I can read the fields from the SQL database and yet write in my comments to the local table. I am using a select query.
Thanks for the help.
Jim
Hi helpers,
I need to have a specialized field for my voucher's local date which is totaly based on different calendar and formats with the system's. (English)
So I created a feild in my table, as text data, and gave it a YYYY/MM/DD input mask in forms, but this field in many places like combo boxes, and reports controls is shown as a number, such as 31940. pls note that there is no similar calendar format in Windows system for my purpose.
How can I avoid this, I mean I need this text (my local calendar date) to be shown as it is and entered. I dont need this feild for calculations, so text is appropriate.
thanks in advance.
I'll try to explain what I'm trying to do. I want to use the MS Access (2010) application residing on the remote server on a Access database(accdb) that is located on my local (home) PC. I copied the database from the server. How do I get Access (running on the server) to work on my copy of the database?
I tried using my local (on my PC) copy of MS Access (2007) to work on the database, but but I get file errors (file paths look like server paths).
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
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
Hi All;
Any advice would be much appreciated….
I have a db of products which drives an ASP website, and includes links to images, which I have now stored locally (with the image path amended to point to the local images folder) along with the images folder containing all the images.
What I now need to do, is produce Access reports using these images, this I initially assumed would be a simple task, but hasn’t in practice worked out that simple!!
So, I now have local folder containing the images, and the products table with an Image field that stores the local path to the image – How can I now get the record and corresponding image to appear in my report? I’ve tried using an OLE field for the images, but this means updating each record manually (unless there is any way of updating the OLE field from the existing image path field??)
Am I missing something totally obvious here?
Thanks in anticipation!
Hi! I work at a company where a same DB is being opened by more than one user, is there any way to allow many users be working on a same DB?
Actually I cannot even open it if its already opened. It appears another file with a "lock", which seems to be protecting the DB. :confused: