Modules & VBA :: Opening A Recordset Based Upon A Variable?

Jul 5, 2013

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.

View Replies


ADVERTISEMENT

Modules & VBA :: Calendar Entries - Opening Recordset Based Off Of SQL String

Oct 30, 2014

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".

Code:
strSQL = "SELECT tbl1CalendarEntries.ID, tbl1CalendarEntries.Title, tbl1CalendarEntries.StartDate, tbl1CalendarEntries.StartTime, " _
& "tbl1CalendarEntries.EndDate, tbl1CalendarEntries.EndTime, tbl1CalendarEntries.TaskTypeID " _
& "FROM tbl1CalendarEntries " _
& "WHERE (((tbl1CalendarEntries.TaskTypeID)" _
& " Like IIf(IsNull([forms]![frmProductionPlanning]![txtTaskTypeID])=True,""*"",[forms]![frmProductionPlanning]![txtTaskTypeID]))) "
& "ORDER BY tbl1CalendarEntries.Title;"

View 5 Replies View Related

Modules & VBA :: Using Access Recordset As SQL Variable?

Jan 16, 2014

I am trying to create a process which selects a customer's site identifier and uses that to query an Oracle database through an ADODB connection. Which is all well and good until that customer has more than one site.

Here is a simplified version of what I have created:

Code:

'Get site details from current database on basis of company selected on form

Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
Dim RsSites As New ADODB.Recordset
RsSites.ActiveConnection = cnn

[code]....

As I said, this works perfectly when the customer only has one site, but I have failed to find a way to turn a recordset with multiple values into a SQL variable that can be used in the IN clause.

I have attempted to convert the recordset to a string using GetString, but could not find a way to correctly seperate out the records.

View 5 Replies View Related

Modules & VBA :: Opening ADODB Recordset

Jul 2, 2013

I'm building a lab environment into one of my projects. I'm testing the distribution of values over a largish number of attempts to create a unique value.I have a table called LAB_UniqueIDTest, with two fields:

LAB_ID - the string value being tested, and
LAB_UsageCount - the number of times the value has been created.

I'm trying to open the table using the following code:

Code:

' Initialize access to the LAB_UniqueIDTest table
Set rs = New ADODB.Recordset
rs.Open "LAB_UniqueIDTest", _
CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic

I have similar snippets of code all over the application - either with a literal (as here) or as a string parameter.I do not, ever, use the options parameter of rs.open.I get error message "Run time error -2147217900 (80040e14) Invalid SQL statement - expected DELETE, INSERT, PROCEDURE, SELECT or UPDATE

My guess is it's because somehow the Open procedure is trying to interpret the tablename as a SQL statement. But how come it isn't this obtuse at other times?

View 6 Replies View Related

Modules & VBA :: Update Recordset Based On User Input Box

Oct 13, 2014

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?

View 8 Replies View Related

Modules & VBA :: Formula Based On Record Number - Recordset Required?

Dec 14, 2014

In order to arrive at the required solution I have to perform a calculation, using data from each successive table record (in chronological order). The result of each calculation must then be used to arrive at the result for the next calculation.

In other words I have to update a variable based on data from each record, sorted in chronological order, and use the final result to populate a field on a form.

Is it time to break out my copy of "VBA For Dummies" and start learning how to use Recordsets?

View 8 Replies View Related

Modules & VBA :: Update Recordset Based On Max Process Instance From Another Table

Jun 7, 2013

I am trying to update a recordset using VBA based on the max "process instance" from another table. After the code executes, the field I am updating is still blank.

Code:
Set rs = db.OpenRecordset("myTable", dbOpenDynaset)
If Not (rs.BOF And rs.EOF) Then
rs.MoveFirst
Do Until rs.EOF = True
emplid = rs![Employee Number]

[Code] ....

View 5 Replies View Related

Modules & VBA :: Check If Record Already Exists In Recordset Based On 2 Conditions

May 27, 2014

I have a table in Access that I have a form saving new records to. Before this save occurs, I would like Access to check if the account number already exists and if the account does exist if it is outstanding. If both of those conditions are met I would like a message box to display and cancel the save as it is a duplicate. I can't seem to get it to work though.

I was thinking to use a filtered recordset based on one of the conditions and then perform a find on that recordset to see if it is null.

Code:

dim acct as long
dim rstfiltered as DAO.Recordset
Set rstfiltered = CurrentDb.OpenRecordset("SELECT * FROM tblclstrack WHERE [Request Status] <> 'Completed'")
acct = Me.cd_number.Value

[code]...

View 9 Replies View Related

Modules & VBA :: Export To Excel With Variable Filename Based On Query Result

Jan 30, 2015

My access is 2013.

Suppose there's a simple query which has two fields, "year" and "graduates", where "year" can be grouped by "2012", "2013", etc and "graduates" are individual names.

How can I export the list of graduates to multiple excel files, with the filename based on "year"?

I have tried to set the output file in macro as "c:desktop" & query.year & ".xls"

But it's not working and the output filename is exactly "&query.year&.xls "and the file contains all year and all names.

View 14 Replies View Related

Modules & VBA :: Opening Forms Based On Results Of Union All

Apr 13, 2015

I have some code in my database that will open a form based on the result of , I think, a union query.

Private Sub resultbox_DblClick(Cancel As Integer)

'Open report based on the ID from resultbox listbox

DoCmd.OpenReport "ResultsStan", acViewReport, , "[ID] = " & Me.resultbox, , acDialog
'End If
End sub

What I need to do is open one of a number of reports. The report that needs to be opened depends on another value that is passed from the union query - DOCCAT

Being still a bit of a novice I tried...

'If "[DOCCAT]= " & Me.resultbox Like "Standards" Then
DoCmd.OpenReport "ResultsStan", acViewReport, , "[ID] = " & Me.resultbox, , acDialog
'End If

Needless to say it didn't work.

View 6 Replies View Related

Modules & VBA :: Opening File Based On Directory Location

Feb 15, 2015

When creating an incoming inspection database, I want to set it up so when I am in a form to add data, I want to have a command button where I can pull up the associated CAD drawing based on part number and drawing number. I would like to enter the directory location into a table so I could change it if I need to.

So basically I want to go something like this:

.tblDirctoryLocation
txtDirectory

.tblProduct
txtProductName
txtProductNumber
txtProductCADNo

These tables would not be related to one another as there will only be one record for the directory location and multiple records for products.

Somehow I want the db to assemble file location like this:

“EQuality_DirectoryIncoming” & “ProductNumber_” & “CADDrawing” & “.pdf”
End result: EQuality_DirectoryIncomingProductNumber_CADDraw ing.pdf

Is there any way this could be done?

View 1 Replies View Related

Help Opening A Recordset

Jun 30, 2005

When attempting to open a recordset, I get "Run-time error '3709': The connection cannot be used to perform this operation. It is either closed or invalid in this context."

Any suggestions would be greatly appreciated.


Public Function GetTickets(ID, NbrTickets)

Dim rsTicket As ADODB.Recordset
Dim rstAPs As Object
Dim strQry As String

Dim cnnDB As ADODB.Connection
Set cnnDB = CurrentProject.Connection

'Determine how many tickets have already been assigned...
strQry = "SELECT count([ticket number]) as nbr FROM Tickets " _
& "WHERE [Tickets]![MailList ID] = " & ID & ";"

MsgBox (strQry)

Set rsTicket = New ADODB.Recordset
rsTicket.Open strQry


With rstTicket
.MoveFirst
AssignedTiks = rstTicket!nbr 'This is the query result
End With

MsgBox ("Number of assigned tickets is " & AssignedTiks)

If AssignedTiks = NbrTickets Then
'nothing
MsgBox ("Nothing")
Else
If AssignedTiks > NbrTickets Then
MsgBox ("Remove Tickets")
'Remove Tickets
Else
'Add Tickets
MsgBox ("Add Tickets")
End If
End If

End Function

View 1 Replies View Related

Help With Opening ADO Recordset

Jun 14, 2007

I am using the following code to create a ADO recordset. The table in which the SQL refers to is in a front end database where the table is linked to the backend. I have just recently experimenting with ADO and need some help. The following code give me and error "Method 'Open' of '_object' failed. Is there a different way to crate a ADO recordset when the table is located in the backend. Please help

Private Sub Form_Load()
Dim mysql As String
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim PersonnelInfo As String


mysql = "SELECT tbl_Personnel.last, tbl_Personnel.RANK, tbl_Personnel.SECTION " & _
"FROM tbl_Personnel " & _
"WHERE (((tbl_Personnel.RANK)='lt') AND ((tbl_Personnel.SECTION)=[Forms]![sos Personnel]![SchShiftFind]));"


Set rs = New ADODB.Recordset
Set conn = CurrentProject.Connection

rs.Open mysql, conn, adOpenKeyset, adLockOptimistic

PersonnelInfo = rs("last")
Me.Lt1 = PersonnelInfo
End Sub

View 3 Replies View Related

Querynot Working When Opening Into A Recordset

Nov 1, 2006

Hello all,

I have a a form which I use to populate query parameters and send the result to MS Word, where a table is created from the query. The form contains 2 controls: 1) a combo box containing the month number and 2) a text box containing a year value. The form also contains a command button that opens the query into a recordset and then creates the Word table.

The query (qryCostData) contains 2 fields that reference these form parameters. The query works when:

I open the query directly (query window) while the form is open w/ the month and year parameters selected
I open the query directly (query window) when the form is closed but the month and year parameters are hard-coded into the query
I open the query using the command button on the form when the month and year paramters are hard-coded into the query


The query does not work when I open the query using the command button on the form and the query containes references to the month and year parameters from the form. The following error is returned on the following command:

command: rst.open "qryCostData", CurrentProject.Connection

error:

Run-time error: -2147217900 (8004e14)

Invalid SQL statement; expected DELETE, INSERT, PROCEDURE, SELECT, or UPDATE


Any ideas on why it works using the form command button when the parameters are hard-coded but not when referencing the form fields? If I open the form and select the parameters and then open the query through the query window, it works fine.

Thanks in advance.

Ken

View 2 Replies View Related

Opening A Report With Recordset Data

Nov 3, 2004

I have looked through the threads and have not found an answer to my question, so I post it hoping there is an answer as well as documenting useful information for other individuals.

The following code is what I am using to 'pull' data in order to print a certificate. It functions the way I designed it (verified by debug.prints and msgboxes). My question is how do I pass data to a defined report (certificate) based upon the results of a built recordset. When the report opens, the values come up as "#Name?". I'm guessing that the issue is syntax, but I just don't know. Here is the code I have so far (I've even commented it for y'all)...

Looking forward to your comments...

-BT.

Dim RSAgg As Recordset
Dim RSsrc As Recordset
Dim DB As Database
Dim strAgg As String
Dim strSQL As String
Dim strCert As String

'If there is no week number set, drop out
If Not IsNumeric(txtWeekNum) Then
MsgBox "You Must Specify a Week Number.", vbInformation + vbOKOnly, "Required Input"
Exit Sub
End If

' tblAggDesc contains the field names
' that have scores I want to evaluate.
' If someone makes a perfect score,
' then they get a certificate. Fields are
' a1, a2, a3, b1, b2, b3, c1, c2, c3.
strAgg = "SELECT tblAggDesc.AggCourse, tblAggDesc.AggDesc FROM tblAggDesc;"

Set DB = CurrentDb()
Set RSAgg = DB.OpenRecordset(strAgg)

RSAgg.MoveFirst
Do While Not RSAgg.EOF
If (Right(RSAgg!AggCourse, 1) > 1) Then 'rounds 2 & 3 contain additional information that is printed on certificate
strSQL = "SELECT tblScores.HEDR, tblRoster.Fname, tblRoster.Lname, tblScores.WeekNo, tblScores." & RSAgg!AggCourse & ", tblScores." & RSAgg!AggCourse & "X AS AggCourseX " & _
"FROM tblRoster LEFT JOIN tblScores ON tblRoster.HEDR = tblScores.HEDR " & _
"WHERE (((tblScores.WeekNo)=" & [txtWeekNum] & ") AND ((tblScores." & RSAgg!AggCourse & ")=100));"
Else
strSQL = "SELECT tblScores.HEDR, tblRoster.Fname, tblRoster.Lname, tblScores.WeekNo, tblScores." & RSAgg!AggCourse & " " & _
"FROM tblRoster LEFT JOIN tblScores ON tblRoster.HEDR = tblScores.HEDR " & _
"WHERE (((tblScores.WeekNo)=" & [txtWeekNum] & ") AND ((tblScores." & RSAgg!AggCourse & ")=100));"
End If

Set RSsrc = DB.OpenRecordset(strSQL, dbOpenDynaset)
If Not (RSsrc.BOF And RSsrc.EOF) Then
RSsrc.MoveFirst
Do While Not RSsrc.EOF
If (Right(RSAgg!AggCourse, 1) > 1) Then
strScore = RSsrc!AggCourseX & "X"
Else
strScore = ""
End If
'MsgBox RSsrc!Fname & " " & RSsrc!Lname & " " & RSsrc!WeekNo & " " & RSAgg!AggCourse & " " & strScore & " " & RSAgg!AggDesc
strCert = "Fname='" & RSsrc!Fname & "' AND Lname='" & RSsrc!Lname & "'" & " AND WeekNo='" & RSsrc!WeekNo & "' AND XCount='" & strScore & "' AND AggDesc='" & RSAgg!AggDesc & "'"
'Debug.Print strCert

' this is the point that I have problems.
' I want to pass RSsrc!Fname,
' RSsrc!Lname, RSsrc!WeekNo,
' RSsrc!AggCourseX, RSAgg!AggDesc to
' the report.
DoCmd.OpenReport "rptCleanTarget", acViewPreview, , strCert

RSsrc.MoveNext
Loop
End If

RSsrc.Close
RSAgg.MoveNext
Loop

RSAgg.Close
Set DB = Nothing

View 6 Replies View Related

Modules & VBA :: How To Extract Recordset From Subform Into Recordset Object

Aug 14, 2015

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

View 4 Replies View Related

Modules & VBA :: Error 91 - Object Variable Or With Block Variable Not Set

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

Modules & VBA :: Sorting / Object Variable Or With Block Variable Not Set

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

Modules & VBA :: Update Contents Of Variable But Not The Variable Itself?

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

Modules & VBA :: Can Use Result Of One Recordset For Other Recordset

Jul 7, 2013

I want to write a email where there are 2 or 3 different ordernumbers for same email, i want to include the email in the mail part as single column table. how to do it? also can i use result of one recordset for other recordset?

View 1 Replies View Related

Filter Based On Another Forms Recordset

Nov 30, 2005

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. :)

View 4 Replies View Related

Report Based On Query Variable Issue

Jan 31, 2006

I have a report that looks at a query. The base data contains a field called 'functlocation'. Now this data can vary in length and looks something like the following

12080026335PDPV5448-1
12080025507PUMP
12080004003BUILDINGS
12081925507PUMP
12081925507PUMP

Now the first 6 digits represent a particular building no. There are actually 6 buildings. When I run the report I am trying to get the report only to show all tasks against 120819 but with a wildcard at the end. I created a mini table with the 6 building numbers in, but i can't work out how to incorporate the wildcard into a parameter query.

regards

vipa:)

View 5 Replies View Related

Append Query Based On Variable Data

Feb 27, 2006

I am trying to write an append query which is based on a table (PA0041)having muliple columns. The columns are DAR01, DAT01, DAR02, DAT02, etc etc.

The data for DARnn will contain values such as S1, S2, S3 etc. For each of these values, there will be a corresponding date in the corresponding field DATnn.

The table PA0041 looks something like this:

Employe Number, DAR01, DAT01, DAR02, DAT02, DAR03, DAT03 ...
1, S1, 21/02/2006, S2, 13/01/2006, S3, 10/01/2006
2, S1, 15/02/2006, S2, 09/03/2006, S3, 18/02/2006
3, S1, 06/01/2006, S2, 16/02/2006, S3, 01/03/2006
4, S2, 03/03/2006, S3, 26/01/2006

There may be up to 10 columns for each - i.e. DAR01 up to DAR10.

I can append them into 1 append table if I write 10 queries. What I would like to know whether it is possible to do this using 1 query. I need to write both the date type and the date into the table.

In addition I need to know whether any employee is missing any date types. If I have a table called tblDateTypes which holds the date types that I would expect the employee to have as a minimum, how can I check for any employees who do not have a required date type.

If the data contained in tblDateTypes is as follows:

DateType
S1
S2
S3

In the above example I would expect the end result to flag the fact that employee 4 does not have date type S1.

Let us assume in the above example that the employees all exist in the table PA0041 - even if they don't have all the required date types.

Let me know if you need more information.

Thanks,

Evan

View 3 Replies View Related

Trying To Update Rows Based On A Count Variable

Sep 1, 2006

Apologies if this is a basic question. Here's my situation:

I'm trying to update rows in a table based on a count of items in the same table. The table in question contains order line items. On each line item, I'd like to store the total number of items attached to that order (because it affects how the individual line items are processed).

I have a query that seems like it should do the trick, but Access doesn't like it:

UPDATE sales AS S1 SET S1.EXPC = (select count(*) from sales S2 where S2.order_id = S1.order_id AND S2.product_code = "EXPC");

Here's the error I get:

Operation must use an updateable query.

Thoughts?

View 5 Replies View Related

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;")

Me.txtserver = rs!Server
Me.cmblocation = rs!Location
Me.cmbtype = rs!BackupType
Me.cmbtype = rs!Type
Me.cmbsize = rs!Size(GB)
Me.cmbassign = rs!Group
Me.txtrequiredby = rs!Date_required_by
Me.txtrequestedby = rs!Requested_by
Me.txtrequest = rs!Date_Time_Request
Me.txtnotes = rs!Notes

'Clear fields on form to indicate write has occurred
txtserver = ""
cmblocation = ""
cmbtype = ""
cmbsize = ""
cmbassign = ""
txtrequiredby = ""
txtrequestedby = ""
txtrequest = ""
txtnotes = ""

'Close recordset and database
rs.Close
db.Close
MsgBox "This information has been succesfully saved"
End 'return user back to form
End If

End Sub

[B]Method 2
Private Sub Save_Record_Click()
'Save all entered information to tblprocess request and tblBackupRequest

Dim db As DAO.Database
Dim sqlStatement As String
Dim saverecord As DAO.Recordset

messageusr = MsgBox("Save this infomation?", vbYesNo + vbExclamation, "Warning you are about to Save this information")
If messageusr = vbYes Then

sqlStatement = "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;"

Set db = CurrentDb()
Set saverecord = db.OpenRecordset(sqlStatement)


saverecord.AddNew
saverecord(0) = txtserver
saverecord(1) = cmblocation
saverecord(2) = cmbtype
saverecord(3) = cmbtype
saverecord(4) = cmbsize
saverecord(5) = cmbassign
saverecord(6) = txtrequiredby
saverecord(7) = txtrequestedby
saverecord(8) = txtrequest
saverecord(9) = txtnotes
saverecord.Update 'Write new record to database

'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:

View 14 Replies View Related

Forms :: Filtering A Form Based On ADODB Recordset?

Oct 16, 2013

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.

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved