Using RecordSet To Insert New Data Into A Query Based On Two Forms
Feb 25, 2007
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:
I`m currently having the problem to export data from an SQL server into a table. I managed to open a recordset but I`m incapable of adding the recordset to an existing table. I found similar threads but I am still not able to generate functioning code.
Code: Function fDAOServerRecordset() Dim db As DAO.Database Dim dblcl As DAO.Database Dim rssql As DAO.Recordset
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 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 need to devise a query to append data for a particular Department_ID from TBL_NEWDATA to TBL_PERSON_ALLOCATIONS where that data does not already exist there. i.e. for Department_ID 'Research', I would want to append 'Person_ID', 'Department_ID' (in this case: 'Research') to TBL_PERSON_ALLOCATIONS for any tuples not already held.
INSERT INTO TBL_PERSON_ALLOCATIONS (Person_ID, Department_ID) SELECT Person_ID, Department_ID FROM TBL_NEWDATA WHERE TBL_NEWDATA.Department_ID='Form...'
[code]...
This Query takes a single argument from a control (Forms!Main!IN_Department), and this is the Department_ID to be updated.Is there any way to do this using a single query or will I have to use sub queries? I'd hoped not to as to keep the database as concise as possible.
I am trying to pull the sum of document count from the SumTotalPerf query where the datereceived in the query matches the date on the form and the BookedInID in the query matches the BookedInID on the form, at the moment Text27 just displays as blank with no error messages displayed so I am lost as to what im doing wrong, Ive double checked all the spelling for my column names etc and all is correct.
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 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?
Code: Function Write_rstADO_to_CurrdB_Table() 'Assumes you have already setup a DSN to your Server 'Assumes YOURDESTINATIONTABLE is the same structure as your SERVER.TABLE Dim cnnADO As ADODB.Connection Dim wkspDAO As DAO.Workspace
I have a combo box Customer_Name on Order Form; and I want it open the Customer Form when an user insert a new customer to input all data. This seems simple at first, but problems are these:
1- whenever an user opens Customer Form on a new customer, the user would have to make the input once again (at least retype the customer's name on the field).
2- since the Customer_Name and other fields are required in the table, the Order_form would not allow the user to close it unless all field are filled properly.
Dilemma is you have to fill in the Customer_Name field before the table refresh all records and display it in the combo box on Order Form
I am new with ms Access. I am using 2013 version and stuck with "pretty" easy task.
When I create the form, the values are inserted/updated constatly as I leave the input components. However I would like to insert/update the record only when I click the button. How to do that?
I have a form with a subform. I want to use the main form to insert new data and the subform to show all records that are there. One could say that the after inserting a new record with the fields in the form and save it, it should appear in the subform datasheet view.
I'm on my way on creating a simple Database for a company. This database contains several tables, one of the tables will record information about the training that had been completed by each employee. There are about ten sections of training that should be completed.
For instance, I have two tables called 'Development' and 'CSA_Lisence'. 'Development' is the table that record the information about training which containing ten checkboxes (which represent ten sections of training) and CSA_Lisence will be automatically requeried when all of the checkboxes on Development are fully checked.
Here's the step I've been worked on :
First, I made a function called 'CheckCompletion' to ensure whether all the checkboxes are checked :
Code: Public Function CheckCompletion() As Boolean Dim blnComplete As Boolean Dim strCompletionSummary As String strCompletionSummary = Basic_Inspection & Certifying_Staff & Safety_Management_System & Regulation_Part_145 & Part_M & EWIS & Fuel_Tank_Safety_Level_2 & Dangerous_Goods & Human_Factor & Basic_Supervisory_Training
[Code] ....
Second, I made a function called 'UpdateEmployee' to handles if all boxes are checked :
Code: Public Function UpdateEmployee() Dim emp_numb As Long Dim emp_name As Long Dim strsql As String emp_numb = [Forms]![development].[employee_number].Value
[Code] ....
Then, I put this code on every checkbox's after update event (example only) :
Code: Private Sub Basic_Inspection_AfterUpdate() Call UpdateEmployee End Sub
The problem is, nothing happened with the tables. However, when I managed to remove the 'If checkCompletion' condition, it worked and the 'CSA_Lisence' is requeried, but I will have ten multiple records with same contents (I just need one record per employee). I guess there's something wrong or missed in my code. Or i need to remove something?
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?
I have a query that creates counts of fields based on the data in other fields, basically it tells me that in a table there are two entries with value ABC????? and three of DEF????? , the query works perfectly.
When I create a form to display this data and base the form on the Query I keep getting a message box asking for the ID (key field) from the base table.
If I type * in the box (to denote all values) and press enter I get the results expected.
now instead of 4.5 for transaction amount, i want to get the data from another query. My other query gives off one row and 1 column which is called "price per night" The other query name is Booking Price per Night (it uses the same [Booking Ref] Parameter.
I hope someone can point me in the right direction
Simple example is I have say 3 recordsets open (they are opened once as they are refered to many times) - they are open early in the form (in this example 3 price lists that are applicable for a customer) (if the syntax of the select is slightly wrong I'm just showing to 'prove' the concept).
Dim db As Database Dim rs1 As Recordset Dim rs2 As Recordset Dim rs3 As Recordset Set db = CurrentDb() Set rs1 = db.OpenRecordset("SELECT * FROM Prices WHERE (((Prices.ID)=1));") Set rs2 = db.OpenRecordset("SELECT * FROM Prices WHERE (((Prices.ID)=2));") Set rs3 = db.OpenRecordset("SELECT * FROM Prices WHERE (((Prices.ID)=3));")
What I want to do is have a central function that I can pass a list I wish to process/do something with aka. The 3 recordsets are the same except for the where criteria.
private sub GETPRICEFROMLIST(pricelist as long)
dim rs as recordset
set rs = Recordset("rs" & pricelist) .....
I put the above to show what I'm trying to do but of course that doesn't work There won't be any updates to these recordsets only reading of data.
I have a form in my front end database that is supposed to allow a user to search for a record based on account number and then make changes to the that record and for it to save in the back end database. I have the search function working where it populates different input boxes on the form with what is stored in the back end database but I cannot get the update function to work. I have tried to assign each input box with a variable and then run an Update SQL function to update each of the fields but the updates are not storing. Any example of a successful update statement that uses VBA variables in it or a way to update a specific record via a recordset type function?
I am currently developing a calendar and am trying to open a recordset based off of a SQL string. When I deleted the Where part of the SQL statement, the code ran fine. So I am pretty sure that the problem lies within the Where part of the code. I use this code to filter my query based on txtTaskTypeID but if the value is null then the query is suppose to return all values. I keep getting Run-time error"3061: Too few parameters. Expected 1".
