Populate Recordsets From Query ?
Oct 27, 2005
Hello all
Im have a table that contains contract that are active or not (tblContract) and a table that contains my every day data for each active contract (tblFldDat). Every day i need to populate my tblFldDat with active contract number. that will result in a nice continuous form that i will be able to fill every day. i need to do that every day (meaning.. repopulate from active contract). BTW... the contract may change from active to inactive everyday. Thats why i need to redo everyday.
My question is...how do i populate a set of records from a list of contract ?
i hope its clear enough cause..i realy dont know how to explain it in another way :(
View Replies
ADVERTISEMENT
May 16, 2013
What really is the best way, programming-wise and user experience-wise when presenting records in a form:
1) Use the form with sub-forms to show records. Add new records via a popup form and use recordsets in VBA to allow validating.
2) Use unbound controls on a form and populate with pure VBA recordsets. For data with more than one row use list boxes. A popup form used for adding new or editing etc using VBA.
3) Another I haven't thought of; new or a combination of above?
View 2 Replies
View Related
Dec 11, 2012
Is there a common answer or design mistake that would cause a form to return a different (much higher) record set than that if the query is ran by itself. The query is the control source for the form.
View 3 Replies
View Related
Aug 9, 2005
:eek: Trying to access a field in a table using a select case, can anyone tell me what the problem is. I get a "No Current Record Error" when I run the code
Select Case intTMO_RATE
Case 5
strNew_PromoID = rst.Fields("5YR").Value
Case 7
strNew_PromoID = rst.Fields("7YR").Value
Case 10
strNew_PromoID = rst.Fields("10YR").Value
Case 15
strNew_PromoID = rst.Fields("15YR").Value
Case 20
strNew_PromoID = rst.Fields("20YR").Value
End Select
View 2 Replies
View Related
Feb 14, 2006
Hello,
I'm going to try to explain this as well as I can. I am also attaching a DB for reference. I am using a version of the
Code:Private Sub lstAllCenterNames_AfterUpdate() ' Find the record that matches the control.Dim rs As ObjectSet rs = Me.Recordset.Cloners.FindFirst "[Center] = '" & Me![lstAllCenterNames] & "'"If Not rs.EOF Then Me.Bookmark = rs.BookmarkEnd Sub
When you look at the DB, the form1 works fine, but is not pulling all of the data that i want. The frmMain is pulling from a query which i have setup to combine 2 columns from 2 different tables and list them into 1 single column and lists in the form all the data that i want to show. When using the query2 as the record source for the form, I can't get the record selector to update when i select new items. Just a note, the tables in the real DB are actually linked to an oracle DB so I am not able to make any changes to table structure. I have just copied only the information needed for this sample DB. Let me know if more explanation is needed.
View 4 Replies
View Related
Nov 21, 2007
Hi All,
Can someone point me in a direction to get some good information on how and when to use recordsets? Ive gotten by without having to use them for the most part up until now.. but the inevitable has happened and i figure i might as well learn to use them
for the most part i know When to use them, but its getting into the how and where to use it for the most part.
simple examples or even perhaps a tutorial would be great! thanks guys
View 4 Replies
View Related
Aug 24, 2006
Hi all,
Anyone know how to open 2 recordsets eg recordsets of 2 tables at same time, and also how to update 2 different fields at once,eg in one line, at the moment I can only open a single recordset and I have 2 docmd.runsql "update etc" commands
Dim TableName As String
Dim dbname As String
dbname = "c:studentswithdisabilities"
TableName = "InputStudent"
Dim CustDB As Database, CustTable As Recordset
Set CustDB = DBEngine.Workspaces(0).OpenDatabase(dbname)
Set CustTable = CustDB.OpenRecordset(TableName, dbOpenTable)
DoCmd.RunSQL "UPDATE InputStudent SET InputStudent.photoBalance = InputStudent!photoBalance-[Forms]![photocopy]![txttop] WHERE [InputStudent]![TNumber]=[Forms]![photocopy]![ComboTnum];"
DoCmd.RunSQL "UPDATE PhotocopyTable SET PhotocopyTable.RemainingBalance = [PhotocopyTable]![StartingBalance]-[PhotocopyTable]![TopupAmount];"
When I run this code using the (Add Record) click event on a button, it performs the first update but it doesnt perform the second until I add the next data record,
Any help much appreciated
View 2 Replies
View Related
Jul 21, 2005
Hello all,
Here is the code adding a new recordset to the table from a form : -
-----------------------------------------------------------------------------------
Private Sub Command21_Click()
Dim Db As DAO.Database
Dim Rs As DAO.Recordset
On Error GoTo Err_Command21_Click
Set Db = CurrentDb
Set Rs = Db.OpenRecordset("tbl_ProjResou", dbOpenDynaset)
' Create a new record.
Rs.AddNew
Rs![ProjectNr] = Forms!frm_Projects![ProjectNr]
Rs![ResourceNr] = Me.ResourceNr
Rs![Hours] = Me.Hours
Rs![reserve] = Me.reserve
Rs![assign] = Me.assign
' Save the record.
Rs.Update
Rs.Close
MsgBox Err.Description
DoCmd.Close
Exit_Command21_Click:
Set Rs = Nothing
Set Db = Nothing
Exit Sub
Err_Command21_Click:
' An unexpected error occurred, display the normal error message.
MsgBox "Die Ausgewählte Ressource existiert Schon, bitte neue Ressource auswählen"
' Set the Response argument to suppress an error message and undo
' changes.
End Sub
----------------------------------------------------------------------------------------
With the code above, I can add only one new record at a time.
In the form I have two unbound fields which are date fields and are entered by the user. I want the date difference between these dates in number of Months (obviously rounded up) and use the above code to add so many number of new recordsets to the table. For example : if date difference is 12 months then the code should add 12 new records to the table.
As I am new to VBA, I dont know how to customize the above code for the above condition. Could anyone pls help me...Please
Thanks in advance.
View 1 Replies
View Related
Apr 2, 2006
Greetings, I have a pretty remedial question, basically I'd like to know how to get a form and its fields to render the change when you call a RecordSet.MoveNext or a MoveLast, basically mimicking the recordset control at the bottom of the form?
View 5 Replies
View Related
Feb 17, 2005
I have two tables in my database. one is linked to a Client.txt and the other is tblNewClient.
the idea is that this database is a portable version of the actual database, meaning that it will be operating on its own without a connection to the actual back end.
Now the problem is when a new customer is added, i want it to add itself to the tblNewClient.
but when i go into my Find CUSTOMERS FORM i need to be able to search threw both tables.
i want to creat somthing like a union query but in vba.
I figure i need to have 2 recordsets in vba. one for each table and then i need to have a 3rd recordset wich will hold all the info.
im not sure how to copy a whole recordset and set to another table.
Im thinking of somthing like
set RsAll = RsClient & RsNewClient
This obviously does not work but it is what i want to accomplish ,and i want the Rsall to be an actual table and not a public Recordset
View 3 Replies
View Related
Feb 21, 2007
My query randomizes the records, but i want it to be different
each time the page loads/refreshes.
Query:
Code:"SELECT * FROM tblImages ORDER BY Rnd(ID)"
I have tried many variations but cannot find one that works,
anyone here know of one that will achieve this.
Thanks
Shem
View 3 Replies
View Related
Nov 24, 2006
I have just been working with some temporary recordsets in access 2000.
After working with the recrodsets each is closed and set to nothing but this leads to bloating of around 20Mb on a 70Mb backend database.
So I (in messing around) I added "DoEvents" after closing each temporary recordset and the bloating reduced to just 8Kb.
Is it normal practice to force the closure of temporary recordsets before moving to the next step using DoEvents, because this certainly seems to be suggested by this result?
Vince
View 2 Replies
View Related
Nov 19, 2004
I am working on searching my customer number in my billing table and finding the appropriate customer and then pass the customer number onto my shipping table to find the corresponding shipping data to go with my billing data. My problem is this :
When I find my customer in my Billing table ( searching through field0 = CustomerNumber)and then select it, it won't pass the customer number to continue to search field0 in the shipping table for customers that have a # in any of the cell values of the recordset.
Is there a work around for special characters in cell data ie) #
Any sugestions or comments would be greatly appreciated.
View 4 Replies
View Related
Jun 13, 2005
hi,
i'm trying to locate a record by id using a recordset clone. when i debug the code, i get a "type mismatch" error...
what am i doing wrong??/
here is the code:
Code:Dim rsCur As RecordsetDim strRecord As StringDim Bookmarkstr As StringstrRecord = Me.List26.ValueSet rsCur = Me.RecordsetClonersCur.Find "id =" & strRecord Bookmarkstr = rsCur.BookmarkMe.Bookmark = Bookmarkstr
any ideas??
thanks
*j
View 4 Replies
View Related
Sep 27, 2005
Need a little help with a record selector.
I ask it to find a record and bookmark it. No problem.
If record doesnt exist I get the value from a control and run a Insert Into command into my table creating a new record with that case #.
Now how do I modify the code below to make the new record just inserted into the bookmarked record. See sample code below. I'm not too practiced when it comes to this recordset business.
Any help is appreciated
Private Sub FindTheRecord()
' Find the record that matches the control.
Dim rs As Object
Dim Answer As String
Dim aSQL As String
Set rs = Me.Recordset.Clone
rs.FindFirst "[CaseNo] = " & Str(Nz(Me![CaseNo]))
If rs.NoMatch Then
Answer = MsgBox("No Matching Case Number Found." & vbCrLf & "Would you like to start a new" & vbCrLf & "record using this case number?", vbYesNo)
If Answer = 6 Then
DoCmd.SetWarnings False
aSQL = "Insert Into Main ([CaseNo])Values ([Forms]![frmMain]![CaseNo]);"
DoCmd.RunSQL aSQL
DoCmd.SetWarnings True
DoCmd.GoToRecord acDataForm, "frmMain", acLast
Code: Original - Code ' does not recognize the last record just added to the table, is there a command I can use here? ' does not recognize the last record just added to the table, is there a command I can use here?
Else
MsgBox "Action Cancelled"
CaseNum = ""
CaseNumYear = ""
DoCmd.GoToControl "CaseNum"
End If
Else
Me.Bookmark = rs.Bookmark
Call EnableControls
End If
End Sub
View 3 Replies
View Related
Oct 15, 2005
Help needed on Access development.
I am endeavoring to develop a set of tables and forms to control a fireworks display. Here is my problem Master form hold display data (IE Location and date) and customer info (no problems here) the sub form holds the the products and how they are fired. This is my problem This form shows several fields Auto number for key field, number for child link (Display ID), number for data, number for data, text and the next is also number (This field needs to be consecutively numbered for each display) therefor on setting the form property to open as new form (Display)the first row of the record set begins with the first shot. How can I code the procedure and what property of the control box should be used.
View 4 Replies
View Related
Oct 20, 2014
I have a form based on an underlying query in a .mdb database. I want to be able to navigate backwards and forwards in the form using .movenext and .moveprevious etc.
Which sort of recordset should I use? I've seen discussions about ADO and DAO, and have come across the term "forward-only", which I'd not been aware of before.
Having made the choice, what is the correct syntax for opening the appropriate recordset, and also for .movenext and .moveprevious etc?
View 6 Replies
View Related
Dec 13, 2005
I'm trying to use a RecordSet to pull data out of an Access DB. I need to compare a field in the DB to a text box in my form. This is what I have so far.
Dim Hardware As Database
Dim Records As Recordset
Dim strSQL As String
Set Hardware = OpenDatabase("c:hardware.mdb")
Serial.SetFocus
strSQL = "SELECT * FROM Computers WHERE Computers.Serial = " & Serial.Text
Set Records = Hardware.OpenRecordset(strSQL)
When I try to run this I get a data type mismatch. Some explanation.
Computers is a table, Computers.Serial is a field in that table. The "Serial" in Serial.Text is a field in my form. You may ask why I'm using the "&" operator instead of just putting it all in the string. The reason is that I cannot figure out how to get VB to treat "Serial.text" as a variable and not a string literal. When I try to put it in single quotes (') I get an error saying the OpenRecordSet call requires an argument, and I'm not providing one, meaning I'm quoting it wrong somehow. Any ideas?
View 2 Replies
View Related
Apr 21, 2012
I'm trying to create a log in form for my database using a tutorial I found here. [URL] ..... I got it working fine, but when I split my database into a front end and back end file I could no longer retrieve the record set for this code. I receive the error "Item not found in this collection" on the highlighted line of code below. I'm at a loss as to how I would retrieve the record set with a split end database. The code used in the log on button is below:
Private Sub submitButton_Click()
Dim dbs As Database
Dim rstUserPwd As Recordset
Dim bFoundMatch As Boolean
Set dbs = CurrentDb
[Code] .....
View 2 Replies
View Related
Sep 2, 2013
When I try to compile the code below I get the error message 'Sub or Function not defined'. I'm using Access 2007.
Code:
For x = 0 To 20
If Nz(rec1(x), "") <> Nz(rec2(x), "") Then
DoCmd.SetWarnings False
strSQL = "INSERT INTO tblResults ([Version], CUSTOMER_ID, CUSTOMER_NAME, CUST_LONG_NAME,
[Code] ....
What I'm trying to do is compare two recorsdsets and if it finds a difference, record the result line from both recordsets in a new table. This worked fine until I tried to go beyond more than 21 records from recordset 2 to publish to the Results table.
The problem arises in the 2nd INSERT INTO... statement. I get the error and it highlights the rec2(20) value. I can enter anything after it (it doesn't have to be a recordset value) but the error still occurs and it wont compile. If I close the statement at Rec2(20) it works fine.
View 6 Replies
View Related
Apr 24, 2014
I have 3 tables; tblProjects, tblTesting, and tblEmployees.
The issue I'm having is that I've created a form which collects testing information from testers that test a particular project. Each project can have multiple testers. tblProject and tblTesters have a one to many relationship and the form (frmTesting) consists of a main form for project info and a subfrom for testing info.
For each project tested there are 4 possible outcomes. 1st not all testers have tested to project and that case project status is "InTesting", 2nd all testers pass testing in that case project staus is "Waiting Final Approval", 3rd all testers fail testing in that case project status is "Maintenance" and last some testers pass and some fail in that case project status is "IPR".
Here is my code for a button I've placed on the sub form.
Private Sub BtnTest_Click()
Dim db As DAO.Database
Dim rsSQL As DAO.Recordset
Dim intMax As Integer
Dim strSQL As String
Dim csSQL As String
Set db = CurrentDb()
[Code] ....
My select statement for my recordset isn't working and I've tested it by putting a specific project number in the statement and I still only pulling one recordset.
View 4 Replies
View Related
Apr 22, 2015
Is there any way of opening recordsets as public or global in a form? E.g. if i do something like
Set rs = db.OpenRecordset("TblCustomers", dbOpenSnapshot, dbReadOnly)
the variable rs should be available in all the private sub i have on that form, and i should be able to access records without opening database/ record sets for individual subs.
View 6 Replies
View Related
Sep 9, 2014
I have parameter form that ask for state and begining date. on click it runs a query and creates a report by state that has multiple pages.. I want to create a separate pdf file for is page (which would be by delegate_name).
This is the coding I have so far and am gettting the error '424' object required. This is the coding I currently have. I haven't worked with access in over 9 yrs so am a little rusty with it.
StateAbb = Me.State
PeriodStartDate = Format(Me.Period_start_date, "MM-DD-YYYY")
Dim strSQL As String
Dim db As Database
Dim rs As Recordset
Dim strRptName As String
[Code] ....
View 14 Replies
View Related
May 22, 2013
I have a form and subform. The main form shows some customer details, and the continuous sub form shows that customer's charity donations.The code below runs when the form opens, and binds ADO recordsets to the two forms. The binding appears to be successful.
However whatever I do I can't make the subform update correctly to show the relevant customer donations. For example, when I use the **'d lines to update the link child/master fields, I get a "Data Provider Could Not Be Initialized" error.
Code:
Private Sub Form_Load()
Dim cn As New ADODB.Connection
Dim rsCust As New ADODB.Recordset
Dim rsDons As New ADODB.Recordset
[code]...
View 7 Replies
View Related
Aug 19, 2005
I want to pass a search parameter from a form to a query then use the query results to populate a multi-column list box. So far, I can pass the parameter to the query and generate results, however, I want the query results sent to a listbox rather than create a new window.
I set up the listbox to receive results from the query, but when the form opens the listbox is already populated with arbitrary query results. I want the listbox to be empty when the form is opened, I submit a parameter to a query, and the listbox fills with the results
Additionally, once the listbox is populated, I want to clear the listbox but am having the error: The RowSourceType property must be set to 'Value List' to use this method.
View 2 Replies
View Related
Feb 7, 2006
I have very minimal knowledge of MS Access and I have to create a database to input, update and display project information. How do I write a query to populate fields on a form, from either a single table or multiple tables?
View 3 Replies
View Related
