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
Hello, I hope I have posted this to the correct forum? Anyways, I have started porting my database backend to MySQL after a spate of corruptions and speed degrading daily, I have managed to get most things functional but one thing which I can not get working is an odd thing with the FindFirst statement. I have the following line of code...
rst2.FindFirst "AwaitingStock=True and StockIn=False and DOA=False and Model='" & Trim(Me.Model) & "'"
which works find with Access backend but with MySQL I get the error...
Run-time error '3761': The decimal field's precision is too small to accept the numeric you attempted to add.
This is obviously not the correct error as I am not trying to add anything! If I remove the bit about the Model, the code executes fine, also, if I remove all the =True parts and just leave the Model part everything works fine so I guess it is because I am mixing string and integer fields in the search???
When, id do press the 'RecordWeergeven' button, Microsoft Visual Basic returns with a error. Method or data member not found. I think the error is located bij Me.Keuzelijst0. Do i have to declare a Listbox or create a second recordset. Please help.
Private Sub RecordWeergeven_Click() 'Me.RecordsetClone.Findfirst "[ID] = " & Me.lstList.ItemData(lstList.ListIndex) 'Me.Bookmark = Me.RecordsetClone.Bookmark Dim rst As ADODB.Recordset Set rst = Forms![FMR_users].RecordsetClone rst.FindFirst "usr_id=" & Me.Keuzelijst0 & "" Forms![FMR_users].Bookmark = rst.Bookmark DoCmd.Close acForm, "GaNaarRecord" End Sub
I just discovered the reason why my table has not been working the way I want it to. In my code below, I have set my rst to find the first record of the previous month which in itself is correct, however I just discovered that it is picking up the records in ascending order.
Here is my code: Option Compare Database Option Explicit
Private Sub Button5_Click()
Dim prevMonth As Integer Dim curMonth As Integer Dim prevYear As Integer Dim curYear As Integer Dim CurRecordMonth As Integer Dim rst As Recordset Dim rst2 As Recordset Dim db As Database Dim monthText As Variant
Set db = CurrentDb
'fill an array with the text for months names monthText = Array("", "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
' find previous and current Year and month. If current month = jan then go back to Dec of the year before curMonth = Month(Date) curYear = Year(Date) prevYear = Year(Date) prevMonth = Month(Date) - 1
If prevMonth = 0 Then prevMonth = 12 prevYear = prevYear - 1 End If
' count number of existing records for current year and month ' If DCount("Month", "TTransactions", "month = '" & monthText(curMonth) & "' and year = " & curYear) = 0 Then
If DCount("Month", "tTransactions", "month = " & curMonth & " and year = " & curYear) < 2 Then 'if current month and year does not exist in table ' open table and find last months record Set rst = db.OpenRecordset("tTransactions", dbOpenDynaset) rst.FindFirst "month = " & prevMonth & " and year = " & prevYear ' open table again to write a new record Set rst2 = db.OpenRecordset("tTransactions", dbOpenDynaset) Do Until rst.NoMatch ' loop through all records meeting the criteria rst2.AddNew rst2![TelNo] = rst![TelNo] rst2!Year = curYear rst2!Month = curMonth rst2!Rental = rst!Rental rst2![Fees] = rst![Fees] rst2![Vat] = rst![Vat] rst2.Update rst.FindNext "month = " & prevMonth & " and year = " & prevYear Loop rst.Close rst2.Close Set rst = Nothing Set rst2 = Nothing End If Set db = Nothing
End Sub
Function CallButton5()
Call Button5_Click
End Function
I have put in an autonumber to assign sequential numbers to the records as they are entered. This I had hoped would allow me to sort my query by autonumber, however if the findfirst keeps finding the records in ascending order then I'm lost. Is there any way to get round this?
I am writing an event procedure to check to see if a particular Project number exist in a recordset. I am trying to use the findfirst method and are having some problems. Here is my code.
Private Sub Command3_Click()
Dim db As DAO.Database Dim rs As DAO.Recordset, ProjectNo As String, SqlStr As String, StrProjectNo As String
Set db = CurrentDb() Set rs = db.OpenRecordset("tblTrackingSheetFrm", dbOpenTable) StrProjectNo = Me![ProjectNumber] rs.FindFirst StrProjectNo
MsgBox " Project worksheet already opened by another user."
rs.Close End If End Sub
What this does is check to see if another user has a project open and if so doesnt allow that user to access that project. I am getting the following error when I execute the procedure on the findfirst Code line.
Runtime error 3251 Operation is not supported by this object type. Can someone take a look and see what I am doing wrong. Any help is greatly appreciated.
I have a procedure that searches a table (recordset 1) for values in a lookup table (recordset 2) using FindFirst / FindNext. The routine worked fine until recently, I now have two issues;
I have included a new country in the lookup table - Cote D'Ivoire, now I get an error message when it reaches this name. I guess it's the " ' " that is causing the problem but don't know how to get round it!!??
I have modified the program so I can select the field I want to search from a form (thanks John) but it won't accept the field name as it's not part of the recordset e.g. rstTempTable.findfirst "[Field] = etc. How can I pass the chosen field from the form to the recordset?
I can't seem to figure out the proper syntax for the FindFirst method. I am using several variations of this effort:
Dim dbs As dao.Database Dim rst As dao.Recordset Set dbs = CurrentDb Set rst = dbs.OpenRecordset("tblInvoice", dbOpenDynaset) rst.FindFirst "rst!ID = frmInvoice!txtID"
I get an error message that says that Access doesn't recognize rst.ID as a valid field. But, it most certainly is. I tried substituting tblInvoice but got the same error.
I am trying to locate a record on a multirecord form by using Findfirst method. Here is the code.
Dim UA1 As String, UAE1 As String, UA2 As String, UAE2 As String, UA3 As String, UAE3 As String, apost as string, repl as string apost = "'" repl = "''" UA1 = Nz(Forms(ParName).Form.NAME, " ") UAE1 = Replace(UA1, apost, repl)
[Code] ....
This code sometimes works and sometimes it does not work. The field CNO is a text field of 5 characters but contains the card numbers that is numeric data or nothing.
: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
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.
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
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
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
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?
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
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?
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 :(
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.
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
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.
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?
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?
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.
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
