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?
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.
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
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.
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
Hi guys, I'm a bit confused: I know that opening a bound form will bring down the whole recordset. But if I create a parametered query as the recordsource, will it still bring down the whole recordset first and only afterwards cut down to the records matching the parameters?
Example: I have a clients table with 5000 clients on a backend mdb, and my client lookup form in my frontend mdb. If I open the form looking for just client "Jim Jones" (via the parameter query), will Access bring down all 5000 clients from the backend to my frontend first, and only then seek out "Jim Jones" before discarding the rest of the recordset?
For if this is true then this whole Access business is rather unsuited for networks, even a small one. I hope you guys prove me wrong.
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?
from a table with fields userID and Date (in which any userID has multiple records with different Date values, and other fields) i have built a select query based on Date = one specific value. can anyone give an explanation (for beginners), through a sample code, that expands the query recordset so that the new recordset includes all records per userID that qualified in the prior recordset? below is an example. thank you!
Table with records: userID / Date u1 / d1 u1 / d2 u2 / d1 u2 / d2 u3 / d2 ... 1st Select Query (where Date = d1): u1 / d1 u2 / d1
Desired 2nd Query based off 1st Query (where ?): u1 / d1 u1 / d2 u2 / d1 u2 / d2
I have two tables that have the exact same fields. In table1 I have records that need to be UPDATED into table2. I tried an Update query and out of 600 records only half of those got updated. In my update query I joined tbl1 and tbl2 by Location (LOC) I checked for Nulls, Blanks, spaces, you name it. I can't figure why they all didn't get updated. I created a SQL query and go the exact same results. Ideally, I would like to create something in VBA to do this. I do not have any forms linked to these tables. In all I have about 600 records and 15 different fields that need to be updated. My example is only for one field.
sql example below. Which is the Access Sql in the Query
I am using Access 2003/sp2 I have used this method to access tables directly that are in the same mdb as the form but all of a sudden the recordset open method will not work:
Dim recset1 As Recordset Set recset1 = New ADODB.Recordset
With recset1 .Open "tbl_WasteReportRecs", CurrentProject.Connection, adOpenDynamic, adLockPessimistic .AddNew .Fields("Machine_ID") = Me.lst_Machine.Value .Fields("Date") = Me.Calendar1.Value .Fields("WasteCode_ID") = Me.Lst_WasteCode.Value .Fields("Shift") = Me.lst_Shift.Value .Fields("Employee_ID") = Me.lst_Employee.Value .Fields("WasteWeight") = CInt(Me.txt_Weight.Text) .Update .Close End With
A clue is when I start typing Dim test as Rec I get two Recordset choices I can choose either one Then after I type Set test = New ADODB.Recordset Then I just do test. and Open does not appear just OpenRecordset It is like the Open Method is not available in my collection I have removed ADO 2.1 Reference and added 2.8 but that did not fix If I open a project that I created earlier (possibly in earlier version of Access) the Open method is avalable when I "test".
I am trying to add a new record to an existing table using ADO recordset. i have always used DAO before this and am quite ignorant about ADO methods... this is what i am doing (my table is called 'Transmittals'): Code:Private Sub cmd_savetrans_Click()'Error Handling On Error GoTo cmd_savetrans_Click_Err' Declare Variables Dim rs As ADODB.Recordset 'Dim sSQL As String ' Update Database Set rs = New ADODB.Recordset rs.Open "Transmittals", CurrentProject.Connection, adOpenStatic, adLockOptimistic rs("Transnumber") = Me.txt_transnum rs("Source") = Nz(Me.txt_source, "") rs("description") = Nz(Me.txt_transdesc, "") rs("Recddate") = Me.txt_transrecdate rs("transdate") = Me.txt_transdate rs("calcs") = Me.cbx_calcs' Message box MsgBox Me.txt_transnum & " added." ' Clear recordset Set rs = Nothing with this, when i click the command button, i get the message "xxxx added." But nothing gets added to the table! can someone guide me through this??
I am working on an access project that connects to sql server 2000 using DAO. I am running into a problem with a combo box that has data bound to it. Basically when a user selects an item in the combo box I query the sql data using a linked table, but the recordset always comes back as read only so I can't edit the data. Any ideas on what may be the problem?
Dim qdf As DAO.QueryDef Dim rs As DAO.Recordset Dim SetID
I've got a command button that goods thru all the records in a form to make an update:
DoCmd.GoToRecord , , acFirst Do Until Me.Recordset.eof If Me.invprint.value = true then Me.invprint.value=false End If Docmd.Gotorecord,,acnext
Loop
It work but I get an error at the end, "You can't go to the specified record"
I'm guessing this mean that the Recordset.EOF is not identifying the last record. Is there a way around this? Is the Recordset.EOF only suppose to be used with Table and Queries? I try to just Resume thru the Error but anything freezes up.
Having a problem with my recordset. I have the vba code creating it using adodb.recordset. Problem is its coming up with no records. If I output the strSQL and paste it in an access query and run it, its fine and brings up all the records it should. I must be missing something or have something wrong for vba ? Below is the code. If you see anything off the bat let me know. I have also tried adOpenForwardOnly and get the same results, in fact tried all the Open types with no luck. Thanks so much.
Code:varpar1 = IIf([Forms]![frm_report_Benefits_Log]![checkbox_posted] = False, "(tbl_ARdata_ACF_Flagged.closed_flg) Like '*'", "(tbl_ARdata_ACF_Flagged.Closed_Flg) = True")varpar2 = IIf([Forms]![frm_report_Benefits_Log]![checkbox_inprocess] = False, "(tbl_ARdata_ACF_Flagged.closed_flg) Like '*'", "(tbl_ARdata_ACF_Flagged.Closed_Flg) = False")varpar3 = IIf(IsNull([Forms]![frm_report_Benefits_Log]![combo_region]), "(tbl_ARdata_ACF.region_code) Like '*'", "(tbl_ARdata_ACF.Region_Code) = [Forms]![frm_report_Benefits_Log]![combo_region]")varpar4 = IIf(IsNull([Forms]![frm_report_Benefits_Log]![combo_area]), "(tbl_ARdata_ACF.area_code) Like '*'", "(tbl_ARdata_ACF.Area_Code) = [Forms]![frm_report_Benefits_Log]![combo_area]")varpar5 = IIf(IsNull([Forms]![frm_report_Benefits_Log]![combo_reason_main]), "(tbl_Reason_Codes_lookup.Reason_Code) Like '*'", "(tbl_Reason_Codes_lookup.Reason_Code) = [Forms]![frm_report_Benefits_Log]![combo_reason_main]")varpar6 = IIf(IsNull([Forms]![frm_report_Benefits_Log]![combo_reason_sub]), "(tbl_Reason_Codes_lookup.Reason_ID) Like '*'", "(tbl_Reason_Codes_lookup.Reason_ID) = [Forms]![frm_report_Benefits_Log]![combo_reason_sub]")varpar7 = [Forms]![frm_report_Benefits_Log]![field_from_date_hidden]varpar8 = [Forms]![frm_report_Benefits_Log]![field_to_date_hidden]strSQL = "SELECT tbl_ARdata_ACF.ACF_ID, tbl_ARdata_ACF_Attachments.Attachment_Link FROM tbl_ARdata_ACF_Attachments RIGHT JOIN ((tbl_ARdata_ACF INNER JOIN tbl_ARData_ACF_Flagged ON tbl_ARdata_ACF.ACF_ID = tbl_ARData_ACF_Flagged.ACF_ID) INNER JOIN tbl_Reason_Codes_lookup ON tbl_ARdata_ACF.Reason_Code = tbl_Reason_Codes_lookup.Reason_ID) ON tbl_ARdata_ACF_Attachments.ACF_ID = tbl_ARData_ACF_Flagged.ACF_ID" _& " WHERE ((tbl_ARdata_ACF.Business_Number)=200) AND ((tbl_ARData_ACF_Flagged.Creation_Date) Between #" & varpar7 & "# And #" & varpar8 & "#) AND (" & varpar1 & ") AND (" & varpar2 & ") AND (" & varpar3 & ") AND (" & varpar4 & ") AND (" & varpar5 & ") AND (" & varpar6 & ") GROUP BY tbl_ARdata_ACF.ACF_ID, tbl_ARdata_ACF_Attachments.Attachment_Link ORDER BY tbl_ARdata_ACF.ACF_ID;" Set cnn = CurrentProject.Connection Set rst = New ADODB.Recordset rst.Open strSQL, cnn, adOpenStatic, adLockReadOnly varlinkcount = rst.RecordCount MsgBox varlinkcount 'Start - Print all code If rst.EOF Then MsgBox "no records?" End If
I am having this error which is driving me nuts but I am sure it is quite a simple one. I am attaching a Sample dbase - My command runs of a Form based on a Table. Any help??
I have a table with multiple records per individual.. I need to search through all of their results to flag up those in a query that do not match the approved list..
what is the best way to do this? I am trying a recordset loop but am struggling a bit...
here is my code so far
Private Sub Command0_Click() Dim rst As DAO.Recordset Dim dbs As DAO.Database Dim strSQL As String Dim strPscale As String
strSQL = "SELECT Eng_Speaking, Eng_speaking from qry_Sub1_crosstab_Crosstab;" Set dbs = CurrentDb Set rst = dbs.OpenRecordset(strSQL) Me.Text1 = ""
Do While Not rst.EOF strPscale = rst.Fields("Eng_Speaking") & ";"
rst.MoveNext
Loop Me.Text1 = strPscale Set rst = Nothing End Sub
but I need to somehow incorporate an array into this to check the score...
I am attaching current db below its not the best in the world but it suits me. lol anyway basically my problem is in the form frmAssignPatientMedication i basically want the user to select from the two combo boxes (a patient then a medicine) then click Assign and i want the database to add this info to the table tblPatientMedicine i cant seem to get my head around how the hell to do it! So any help will be greatly appreciated! Any other areas u think i could better in my db just say u'll probably be right! Thanks in advance:)
I have created a recordset and what i want to do is check the field value "SiteRAG" to see if it matches some criteria and if so do some action where the field "SiteID" = the same as a label on a form.
Please see CAPS in middle of code:
Dim db As Object Dim rs As Object Dim intCount As Integer, intRecordCount As Integer, intID As Integer Dim strSQL As String Dim strRAG As Long Dim fldItem As Field
strSQL = "SELECT tblSite.SiteID, tblSite.SiteRAG, tblSite.Active " _ & " FROM tblSite " _ & " WHERE (((tblSite.Active)=Yes));"
Set db = CurrentDb Set rs = db.OpenRecordset(strSQL)
intRecordCount = 0 rs.MoveFirst
CHECK THE FIRST ROW FIELD SiteRAG & IF = TO CRITERIA THEN DO SOMETHING BASED ON A LABEL MATCHING THE SiteID ELSE MOVE TO NEXT ROW
Hi...hope someone can help. I'm a bit new to the programming scene and need some help with a recordset.
The current code that I am using is:
Private Sub Command126_Click() 'September - 2006 - Create Application Form. Dim appWord As Word.Application Dim NoPrompt Dim rstContacts As Recordset Set rstContacts = Me.Recordset
Set appWord = GetObject(, "Word.Application")
With appWord .Documents.Add "C:Documents and SettingsAdministratorMy Documents est.doc" .ActiveDocument.ShowSpellingErrors = False .Selection.Goto wdGoToBookmark, Name:="test" End With
Do Until rstContacts.EOF
appWord.Selection.TypeText rstContacts!ContactName & " " rstContacts.MoveNext Loop End Sub
I want a situation where recordset information can be 'fed' to a word document depending on what is written in control boxes on the form. So, for example if there are three options in a Combo Box, namely:
Option 1, Option 2 and Option 3
I want information from the 'ContactName' control box to be inserted in the 'test' document at specific 'bookmarks' for all forms where 'Option 1' is selected in the Combo Box.
Hope this makes sense....I think I'm getting close but still no joy
