I have an accdb file, holding all the tables on my server. Clients are linked to this one from their workstations. Speed is a constant issue mostly when more users are linked and I'm trying to find ways to make things faster.
Would it be possible to open a recordset when a form is loaded and set it as the form's data source? Would that make operation faster, at all?
The reason this idea came up is that it would be logical to use a small size recordset rather than large tables or queries that takes a while to load during starting form that use tables/queries with large number of records.
My DB has many (!) forms that are based on Crosstabs queries. The headers are years, and therefore my formfields has controlsources like 2014, 2015 and so on.
Example:
The table it comes from looks something like this:
The crosstab then takes the EndYear as a coloumn header. Then my forms use these fields, now named 2014, 2015, 2016 .... and give me a nice looking table showing the accumulated "end" for multiple projects.
The problem is when the year changes after 31/12, all fields must manually be changed. This means I have to manually change 2014 to 2015, 2015 to 2016 and so on in all of my formsfields controllsource.
I have played around to find a way to change the source of the field, and hoped I could say something like =year(now()) and for the next year say =year(now())+1 but this only returns that year as a value, and not as a controlsource...
The query functions nicely (ive changed the system time, and it has no issues with this structure), but the forms source is still in the previous year, and returns and error (of course).
When the year changes past new years eve, so will the headers (fieldname) change... but how to make the form change fieldsource?
I have a SQL table shown on a form which i wish to filter by current logged in user -
I have a local table storing the currently logged in user and all records in the SQL table have a field called userEmail
so - i have forms that take their datasource from a query which then only shows the user his/her records.
SELECT * FROM userDetails, dbo_TrackerMainData WHERE (((dbo_TrackerMainData.userEmail)=userDetails.user email));
that works fine - after much torment, i figured out that you must use the query as the datasource and not the table as the datasource with a filter, because that just didn't work.
anyhoo.
my problem then is that when i use a form in this way - ie, datasource is a query, i can't then modify any data in the table!
I need to have a few checkboxes on the form that set values - for instance archiveFlag and TestFlag are fields and i need to be able to change their value.
If i set the form datasource to the table (dbo_TrackerMainData) it works ok - my check boxes tick and set -1 (but i see ALL records)
if i set the form datasource to the query i see only 'my' records, but when i try to click the checkboxes nothing happens..
I am new to the Access programing. One of our clients wants to export the record set that is being displayed on the form to excel. We are using ADODB Recordset to display the data on the form. We also have some computed columns. Is there any way that I could export the data to excel?
My application crashes when trying to change the value of a text box in a continuous form. Here is the code:
Code: Private Sub cboPoCurrency_AfterUpdate() On Error GoTo ErrHandler Dim rst As Recordset
[Code].....
If I replace .txtUnitCost by MsgBox .txtUnitCost, it loops correctly through each record and returns the value. But if I try to change the value as shown in above code, MS Access crashes! (This is a desktop application with tables linked to SP lists - not a web app)
I have a form that shows records from ADODB recordset.When I try to apply filter to the underlying recordset it works all right but the form doesn't reflect the changes. It shows same rows as before filtering. In debug I can see that the recordset contains only filtered records. Me.Refresh (Recalc, Requery) doesn't work.
Code is as follows:
Dim rs As New ADODB.Recordset rs.Open sql, conn, adOpenStatic, adLockOptimistic Set Me.Recordset = rs
Sub combo_AfterUpdate() Me.Recordset.Filter = "CompanyNo = 123" End Sub
The form is in Continuous forms mode. I cant use DAO because the data comes from SQL server user-defined function.
I have a number of forms that are bound to recordsets as follows:
Dim rs As New adodb.Recordset
sqlQuery = "Select * from myTable" rs.Open sqlQuery, sqlCNN, adOpenKeyset, adLockOptimistic Set Me.Recordset = rs Set rs = Nothing
In Access 2003, users could open these forms and filter on basically any field by using the right click-> Text Filters functionality.
In Access 2010, this functionality appears to work (users can apply the filter and the Toggle Filter button in the ribbon shows a filter is applied), but all of the records are still visible in the form.
Any work-around that does not involve redesigning the form to be non-recordset bound?
Currently, I have 10 Users running a front end form that connect to back end data where they add to current records and eventually check that the record is complete.
I also have an "Apply Filter" button on the right hand side of the form that allows the user to apply filters to the records to show specific data that is not complete.
I am currently hearing that while the user is working, whether they apply filter or not, it seems as though all of a sudden, all of their completed work dissapears from their recordset hence not allowing them to go back to make changes to it if needed. I can only conclude that when a different user clicks apply filter, all users are affect some how... here are some examples of the code for the filter:
Code: Select Case [cboFilterValues] ' Where the user selects a filter parameter from a dropdown Case "All Data" strSQL = "SELECT * " & _ "FROM [Data Table] " & _ "WHERE [Complete] = No
Then it does:
Code: Me.RecordSource = strSQL
I am basically trying to find a way where only the user wanting to view the filter is affected... FYI, the other users arent actually seeing all the filters from the other user filter choice, they simply loose all their completed work from their form.
Hello, Is it possible for a [MSAccess] query to obtain table data from an [external] ODBC Datasource? Something like: INSERT INTO TableA SELECT * FROM ODBC;DSN=MyDSN;UID=;PWD=;.TableA
I found a similar thread but didn't understand the answer which mentioned linking a back-end to the Access Table.
Not sure what section this should go in but it vaguley relates to access and VBA so it's in here.
After a complete nightmare trying to do some decent graphs in ASP i decided to try using excel linked to my Access DB.
I've set up my pivot tables in Excel to link to the Db on the webserver. The user can open the workbook from the website and view the reports and graphs.
The problem is the database contains sensitive data for more than one organisation.
I have a workbook for each organisation selected dynamically when the user logs in to the webpage.
I've set a parameters in the pivot table querys so that all querys will be filtered using the users organisation code.
Trouble is you can access the data source of the pivot table through the wizard and ammend the parameter to view other organisations data.
Is there a way of preventing the user from accessing this feature i.e locking the wizard?
Can anyone help me with setting up a Visual Basic project to where the datasource I use is a Access database on my harddrive. This Access database isn't linked to a server or anything. It's just a database I created and would like for my project to open this file everytime it's attempting to pull data. How can I make this connection? If I am able to use ADO, how do I go about doing this? I would think ADO wouldn't be necessary though.
Special situation: The SQL Server Linked Server across the country is linked to a Read Only Oracle DB. This data pull works perfectly and populates the Subform.
The problem is that Oracle can take 3 to 6 seconds to retrieve the single record depending on the network traffic through a small pipe.
The code below shows the RecordSource for the SubForm. clicking on a list box supplies the value. Then 3 to 6 seconds later, the subform populates.
The actual Recordset for this Recordsource is needed to conduct Validation on each field. Normally this would be on SQL Server, I might just create a Recordset Oject and run this SQL statement again in 1 milisecond. In this case, it will probably take an additional 3 to 6 seconds. Avoiding another lengthy round-trip to Oracle would be prefered.
Goal: How does one grab, clone, or other wise reference the existing recordset for the SubForm?
Note: Immediate Window - One single field can be returned quickly
There are 48 fields that need validation - is there a way to reference the entire recordset?
Immediate Window during Break Mode: ? me.fsubsrNavSHLBHL("NavSH_QQ") NESE ' this is the correct value for the current recordsource
Set a breakpoint right after the line: fsubsrNavSHLBHL.Form.RecordSource = "Select * from vsrNavigatorSHLBHL where Well_ID =" & txtNavWellID.Value
Immediate Window: ? me.fsubsrNavSHLBHL.Form.RecordSource Select * from vsrNavigatorSHLBHL where Well_ID =91229
I am trying to create a (normally simple) application, but not having a solid grasp on the concepts of recordsets. The application is serial number tracking for appliances.
The functionality needs to be able to:Search for serial numbers using partial strings (i.e. serial # "T12395723K39" can be located by entering "3K3")
If the serial number does not already exist, allow for adding it. If data was added erroneously, allow editing to correct it.
The first 2 points are complete but I am struggling with the last. After a user performs a search, I store the results of that search to a temporary table for easy viewing of applicable records.
To allow someone to edit, they simply double click on one of those records to open a seperate form and edit.
When I displayed the content of the temporary table in the form for editing, I found it was not updating the original table.
When I create a separate recordset, using the values of the record selected, the recordset appears to be empty, therefore not displaying the correct record for editing.
My main table name is "SerialNumbers". The code below is triggered when the user double clicks on the desired record to edit.
Private Sub Claim_DblClick(Cancel As Integer) '************************************************* ******************** '* Subroutine to take values from search results for editing record. * '************************************************* ******************** 'Initializing variables Dim ClaimNo, ApplianceName, SerialNo, MakeName, ModelNo, sqlstr As String
I've got a form based on a query. Is there a way I can filter the records showing on the form, based on the records in another form's recordset. I've managed to get the second form to show the same records as the first using me.recordset = forms!otherform.recordset
but I actually want some different fields in my second form so want to base it on a different query.
Can I do something along the lines of... Form1 based on Query1 with fields CustID, Field1 Form2 based on Query2 with fields CustID, Field2 but filtered where CustID in Form1.Recordset? I've probably not explained that very well so please ask if you need more info. :)
I have a continuous form that lists a group of records in a main form based on a query. On each line of this continuous form, there is a button that can be clicked to open the main form based on that record.
This works fine, although it filters the recordset to just that one record. What else do I need to apply the same recordset that is in the continuous form, so the user can also scroll through the records on the main form if they so wish to?
I want them to be able to click on the button in the continuous form, open the form to that record, but also give them the ability to scroll to the other records that were listed in the continuous form. The continuous form I mentioned is its own form seperate from the main form.
I have a main form with combo boxes to filter a subform. I want to be able to export the filtered subform data to a table within the database and only include the filtered dataset not all records.
Basically, I want to be able to click on the New (blank) record button, and then start adding in my Attraction, Date, etc fields in the main form, however I keep coming up with the error:
Cannot add record(s); join key of table 'OrdersList' is not in recordset.
I've gone back and checked all my relationships, and the query the form is based on, and all appears to be working there fine. I can manually enter information on to the tables & queries just fine, (but obviously I don't want other users to be able to see these).
I'm wondering if it's something to do with the Auto Number, which is also my PK in table 'OrdersList'. As the first design of this database had this field set to a Text field and I would manually enter the next sequence and I didn't have any issues adding new Orders to the form.
The form is 'Orders', which is based on a query called 'Orders List'.
I have a following problem: I have a form in continuous mode. Users are supposed to filter data in it to their liking using inbuild filters in Access. After that there is a button that should calculate total weights in filtered records (at this point, I add more calculations once I have solved this issue). Code of button is as follows:
Code: Private Sub btnCalculateWeight_Click()
Dim rst As Recordset Dim weight As Long Dim material As Long Dim subtype As Long Dim locus As Long
[Code] ....
Loop goes through recordset correct amount of times but for unknown reason takes value only from the first record and sums it N times where N is number of records in recordset. Recordset seems to be correctly assigned according to messageboxes I added to pinpoint problem. PotteryWeights is a custom function I made and it works properly.
I have a form that gets its info from a query, I would like to be able to add a new item, Customer, etc, etc. But, when I try to add one (I have a button using VBA code, ill post that at the end) it gives me the error
Code: Cannot add record(s); join key or table tblitems not in recordset.
Code: Private Sub cmdEventNewI_Click() Me.Visible = False DoCmd.OpenForm "frmItemsEdit1", acNormal, , , acFormAdd, acDialog Me.Visible = True Me.lstItems.Requery End Sub ' cmdEventNewI_Click
How it works: You search for a User, and then you search for a Serial.Now, what if I want to edit the table? (inside the subform, the results). I added an openRecordSet, and Parameters, but I still get the error saying that Recordset cannot be updated.Why? I opened and sent parameters but still fails.
I would like to filter my form - AdminForm with a combo box. If the user selects one of the choices.... Open, Completed, Hold then the records would be sorted by that criteria. There is a bound combo on the form now for the user to choose the state that the record is in and it coincides with the sort terms. I also have an unbound combo set up to use for the sort - ComboStatusFilter. I was thinking that an SQL statement for the form properties should work but am not sure how to write it. Is this the right way to go about it? Any help would be appreciated! I have been reading and searching for days but as yet not found what I am looking for, or cant recognize it at least. LOL
This forum has been so useful to me so far... but having searched through a load of topics for a few hours now I just cannot find a correct method of having the ability to update two tables from a form.
I firstly created a query that selected the nessary fields I wish to update from the two tables.
And its apparent that I need to use RecordSet to insert the information from the form into the query.
I have found a few different ways of doing this - none of which work for me :( HELP!
Method 1 Private Sub Save_Record_Click() 'Save all entered information to tblprocess request and tblBackupRequest
Dim db As DAO.Database Dim rs As DAO.Recordset
messageusr = MsgBox("Save this infomation?", vbYesNo + vbExclamation, "Warning you are about to Save this information") If messageusr = vbYes Then
Set db = CurrentDb() Set rs = db.OpenRecordset("SELECT .Server, [Backup Request].Location, [Backup Request].BackupType, [Process General].Type, [Backup Request].[Size(GB)], [Process General].Group, [Process General].[Date required by], [Process General].[Requested by], [Process General].[Date/Time of request], [Process General].Notes FROM [Process General] INNER JOIN [Backup Request] ON [Process General].ProcessID = [Backup Request].ProcessID;")
'Clear fields on form to indicate write has occurred txtserver = "" cmblocation = "" cmbtype = "" cmbsize = "" cmbassign = "" txtrequiredby = "" txtrequestedby = "" txtrequest = "" txtnotes = ""
'Close recordset and database saverecord.Close db.Close MsgBox "This information has been succesfully saved" End 'return user back to form End If
End Sub
and I have even looked into an insert sql statement [B]Method 3 Dim SQL_Text As String SQL_Text = "INSERT INTO Backup Request (Server, Location, Type , Size(GB)) VALUES ('#Backup Request.txtserver#','#Backup Request.txtserver#','#Backup Request.txtserver#','#Backup Request.txtserver#') &" INSERT INTO Process General (Group, Date required by, Requested by, Date/Time of request, General_Type, Notes;" Docmd.RunSQL (SQL_Text, false)
Method 1 seems to be popular but its returning the message Run time error '3061' Too few parameters. Expected 3. :confused:
Trying to use a form based on a table to choose a recordset of values, and pick the ID of that recordset to include in another table as a foreign key.
I am not sure how to even search for what I'm trying to do, though I've tried all the variants I can think of anyway.
The idea being that there are a set of values that are associated with each other, and generally found in conjunction with another set of data. So Table 1 contains data such as this, though this is greatly simplified from the numerics that are actually stored in these fields:
FooID Field 1 Field 2 Field 3 etc, etc... 1 x y z 2 d y z 3 x y q 4 x r P . . .
This data is then associated with the information in table 2, and rather than repeat fields 1-whatever in table 2 I want to use the FooID in table 2 to store the link to all of the subsequent field data. Normalization as I understand it in other words.
I cannot figure out how to pull the table 1 data into a datasheet form and allow someone to choose one of the lines of data, store the FooID into an unbound text box, or some other method, and then run an update query on table 2 to update a field with the FooID for the 12-72 records that are in that table.
Would this make more sense as a combo box in a single form? I've been trying to do this with a datasheet, but I think I'm too limited in appending a check box to an individual recordset this way, or at least I've not managed to get it to work so far anyway.
I have a Product form that shows the details of each of the Products in my Product table. I also have a "Pick a product" combobox (who's rowsource is being fed by the same Products Query that feeds the form). This allows you to go straight to a particular product by selecting its name. So far, so simple.
I've since added a couple of combobox filters which use different columns ("Product Type" and "Collection") to allow the user to get down to a more manageable recordset to then work with.
Of course, because the "Pick a Product" combobox is being fed by the Products Query, the combobox can end up showing products that are not contained in a filtered recordset. This is... sub-optimal.
Is there a way of telling the "Pick a Product" combobox to use the products listed in the recordset as its rowsource rather than the product query? So that it only lists the products the user has filtered down to?
