I have a Suppliers table with 3 fields (code,name,addr).
I have a Form where I need to list all records in this table + a checkbox against each record on which the user will tick for only some suppliers he wishes to select.
How can I do this without this??
I dont want to create a table just for this. Is there a way I can populate these records into a temporary Recordset & Add a field to it, and then assign this recordset to the Form???
:confused:
Hi all,
Not sure where to start on this one!
First I have a main form “FormA” formA has a subform “FormB” in a datasheet format. I have one of the fields in “FormB” Double-Click Event procedure as follows:
Private Sub Results1_DblClick(Cancel As Integer)
Me.Refresh
Dim stDocName As String
Dim stLinkCriteria As String
If panel = 5 Then
stDocName = "Subform5"
ElseIf panel = 8 Then
stDocName = "Subform8"
ElseIf panel = "DOT" Then
stDocName = "Subform5"
ElseIf panel = "Breath" Then
stDocName = "SubformBA"
ElseIf panel = "Hair" Then
stDocName = "Subform5"
Else
stDocName = "Subform10"
End If
stLinkCriteria = "[DSID]= " & Me![DSID]
DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub
This then pops-up a “FormC” (Subform5, Subform8, Subform10, or SubformBA) linked to a specific record from “FormB”. FormC has a drop down field that will allow you to select “Negative or Positive”, and then when FormC is closed, FormB is updated.
This has been working fine for the past 4 months. NO PROBLEMS!!!!
NOW, for no apparent reason when FormC pops-up and I try to click the drop down field; it just BEEPS at me (Almost like the field has locked the user out), then when I close FormC, I get and error: “Recordset is not updatable”. On the FormC (SubformBA), I get no error on close, but I get the word “#error” in the field on FormB.
Has this happen to anyone? Can someone help I have exhausted all thoughts.
Thanks-a-million, ENVIVA
Is there a possibility to use a shorter code for extracting one field from one record, than opening a recordset, getting the field, closing it and setting the recset to "Nothing" again? I'm doing this multiple times in my code and it seems a bit too much for what it's supposed to do.
Code:
sql = "SELECT tblAdFlgDaten.* FROM tblAdFlgDaten WHERE (((tblAdFlgDaten.AuftrID)=" & ABAuftrID & _
") And ((tblAdFlgDaten.Schritt)=2))"
Set RSnap = dbase.OpenRecordset(sql, dbOpenSnapshot)
If IsNull(RSnap.Fields(2)) Then
...
...
End If
RSnap.Close
Set RSnap = 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
Is it possible to retrieve the format of a field when looping through recordset.
e.g you can get the field type by using
Quote:
rs.Fields(y).Type
But the type for decimal or percentage is the same. It is the format that is different
I need to be able to see what is formatted as a percentage and what is formatted as fixed.
I am trying to use a calculated field in a recordset but I am having problem with the script trying to compile.
my record set is
Set rstPrice = db.OpenRecordset("SELECT Period_desc, Price, Round(Price/7,2) AS Daily_rate FROM qryPropertyPriceList WHERE [Our ref] = '" & rstProp![Our Ref] & "' And [Year] = " & rstProp![Next year price base] & " Order By Sequence", dbOpenSnapshot)
The calculated filed I have added in is Round(Price/7,2) AS Daily_rate
If I let it run just with this it runs fine.
and then I try to use this calculated field:
Do Until rstPrice.EOF
Temp = Temp & rstHTML!html35 & rstPrice!Period_desc & rstHTML!html36 & rstPrice![Price] & "-" & rstPrice![Daily_rate] & rstHTML!html37
rstPrice.MoveNext
Loop
rstPrice.Close ' Tidy up
Set rstPrice = Nothing
They it stops running and it all seems to be because of the Daily_rate field
I am using Access 2013 under Windows 7. In my database I have a table, tblStock, with field names Module, Component_1, Component_2, etc. up to Component_50. I also have fields Qty_1, Qty_2, etc. up to Qty_50. These field names are not easily changed as they are constantly updated from another database. I want to create a table, tblTempBOM, using VBA by selecting a particular value of Module selected from a combo box on a form, from tblStock and creating a record with the Fields "Module", "Component" and "Qty" for each Component and Qty from 1 to 50. I am trying to use a From... Next... loop to cycle through the Component and Qty fields and store the data in the new table.
Dim strModule As String
Dim strSQL As String
Dim strQty As String
Dim strEye As String
Dim strTest As String
[code]....
This runs to the point where I try to set the value of rcd![StockCode], but the item rcdStock![strTest] has no value.Can I reference the value of the field in the rcdStock Recordset in some other way that would work?
Error 1: Setting Date Field in Recordset to null
Error 1: Setting Date Field in Recordset to null " data conversation error 3421 "
Solution: If the field is null set it back to itself .
Here is a simple dummy example i wrote to demonstrate the solution ( look for the bold text in side the code )
Code:
Sub Event_btnSaveEndTime ()
dim strEndDate as string
With Form_frmMainForm
strEndTime = .txtEndTime.Value
[code]....
Hi guys,
This is my first post of hopefully many, and I hope to be able to keep visiting and helping others in the future.
I'm building a database at the moment which I've done basing forms on Queries as I'm very familiar with doing so. However, one form in the database if a bit different.
The form in question is for shipping off refurbished units at the warehouse, by adding a SHIP ID to the record for each unit scanned in. The form before creates a recird ubdexed by a SHIP ID in the SHIPPING table, along with some other info such as delivery address and ship date. Each unit will be assigned this SHIP ID by the child form I wish to create, by scanning the Serial Number against it.
Two actions are done on the child form: Read in Serial Number of unit, find record in MAIN table and display key fields such as the units status (i.e. "WIP" or "REPAIRED" into text boxes on the form Allow unit to be shipped (by assigning the SHIP ID to the unit's record in the main table, only if the STATUS field shows as "REPAIRED"
Now, because I want to scan units into the form's Serial Number textbox and do the STATUS field check, then assign the SHIP ID; I need a recordset, right? I've inherited development of datdbases which used recordsets in their forms before, but have never had to create one from scratch. Also I wasn't able to take a copy of the source code of those databases before I left my last company.
Can anyone give me some info on what the bare minimum is to create a recordset, and to use it to save the record once I've made changes.
For your info I would consider myself an Access intermediate, and I've worked with it in various roles and versions for around 5 years.
Thanks,
Gareth
morning
i am adding a new row to an adodb.recordset, one of the fields being a Memo datatype.
all the other fields will write to the database fine, but with the memo, it will mysteriously disappear when i call rs.update
response.write(rs("my_memo_field")) 'give correct output
rs.update
response.write(rs("my_memo_field")) 'gives nothing!
any one else had this happen and have a solution?
cheers
Is it possible to add/edit a record from a DAO querydef that has a calculated field in.
Code:
Set qdf = db.QueryDefs("qryOutput_" & strDept)
qdf.Parameters("Enter Date") = dDate
Set rs = qdf.OpenRecordset()
Using the rs.Edit or rs.AddNew worked fine until I put a calculated field in the query.
The calculated field is just a total of some fields in the table.
I thought this was something to do with dbOpenDynaset but it just keeps having the same error
Cannot update. Database or object is read-only.
I am storing values of pictures and the location of them in a table, this works fine!... using OpenRecordset. The problem is that when the function is called to store the information, it just keeps adding the same values of each file in the folder over and over again in a word "Duplicating" the information.
I have tried various methods using the OpenRecordset, but cannot seem to find the correct manor of applying the code.
Below is the function I have for storing the data...
Code:
Public Sub GetFilesNamesFromFolder(strFolderPath As String)
On Error GoTo ErrorHandler
Dim objFSO As Scripting.FileSystemObject
Dim objFolder As Scripting.folder
Dim objFile As Scripting.File
[Code] .....
I've been building a small app for some friends. At present, they enter "records" line by line into a word processor, then use (archaic, IMHO) macros to produce the desired printouts. They wanted the new data entry UI to mimic that process.
Ok, so I've adopted the datasheet form as the principal entry vehicle. Based on the initial info they gave me, there were two convenient fields to sort on, so I used them in the OrderBy clause of the query which serves as the form's recordsource. I also developed the code to maintain the sort order after they inserted or deleted a record.
Now it turns out they don't require an entry into those fields, and in fact there's no logical field at all on which to order by.
So the question is: how to approach this? From my reading, I can't use the primary key or an autonumbered field, since the former won't necessarily reflect the desired incrementation and the latter only triggers for a new record, not an insertion (is this correct?).
My thought is to add a simple numerical field (which will be hidden from the user) with sequential values. I can do the OrderBy on it, and add code to adjust those values whenever a record is deleted or inserted. (That will require looping through the recordset from the point where the deletion or insertion occurred.)
For instance, is there some way to add a calculated field to the recordsource for this purpose?
I need to come up with a way to derive field X (see below) in a query.
For each change in field A, Set X=1
For each change in field B, X=X+1
The below table shows what the query results should look like.
A
X
B
[code]....
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.
Premy
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 Relatedfrom 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
sql = "UPDATE tbl2 INNER JOIN tbl1 ON tbl2.LOC = tbl2.LOC" & _
"SET tbl2.Name = tbl1!Name" & _
"WHERE (((tbl2.Name) Is Null));"
Example.
Table1
Field1.Names = John
Table2
Field1.Names = "Need to UPDATE the name 'John' here"
I tried the Recordset .EDIT but I couldn't get it to work using two tables.
I'm a bit daft when it comes to recordsets to update a table.
I've searched the forum and can't find what I need. I was hoping I could find help here.
Below is my code I'm trying to use to update two tables. I use a query to get the SQL I needed, but it still isn't working.
I'm updating the tables from fields on an unbound form.
Thanks in advance.
Private Sub cmdAddEntry_Click()
On Error GoTo Err_cmdAddEntry_Click
Dim strSQL As String
Dim cnCurrent As ADODB.Connection
Set cnCurrent = CurrentProject.Connection
Dim rsRecordset As ADODB.Recordset
Set rsRecordset = New ADODB.Recordset
rsRecordset.ActiveConnection = CurrentProject.Connection
rsRecordset.LockType = adLockOptimistic
rsRecordset.CursorType = adOpenForwardOnly
strSQL = "UPDATE tblClaims INNER JOIN tblRCN ON tblClaims.intClaimID = tblRCN.intClaimID" & _
"SET tblClaims.strClaimNumber = [frmLogEntry].[txtClaimNumber], " & _
"tblClaims.strPatientID = [frmLogEntry].[txtPatientID], " & _
"tblClaims.strClaimType = [frmLogEntry].[txtClaimType], " & _
"tblClaims.dtmCancelDate = [frmLogEntry].[txtCancelDate], " & _
"tblClaims.dtmProcessDate = [frmLogEntry].[txtProcessDate], " & _
"tblClaims.strGroupName = [frmEntryLog].[txtGroupName], " & _
"tblRCN.curPaymentExpected = [frmEntryLog].[txtPaymentExpected], " & _
"tblRCN.curPaymentReceived = [frmEntryLog].[txtPaymentReceived], " & _
"tblRCN.strRCNNumber = [frmEntryLog].[txtRCN];"
rsRecordset.AddNew strSQL
rsRecordset.Update
rsRecordset.Close
Exit_cmdAddEntry_Click:
Exit Sub
Err_cmdAddEntry_Click:
MsgBox Err.Description
Resume Exit_cmdAddEntry_Click
End Sub
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??
Thanks a ton!
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
SetID = DLookup("RateSet", "qselFwdrSets", "cusnum=0" & DLookup("cusnum", "dbo_tblOrders", "OrderID=" & OrderID))
Set qdf = CurrentDb.QueryDefs("qselOrderExtraRatesForFunc")
qdf.Parameters("pOrderID") = OrderID
qdf.Parameters("pRateType") = RateType
Set rs = qdf.OpenRecordset()
If rs.BOF And rs.EOF Then
'' didn't added yet
rs.AddNew
Else
'' recalculate
rs.Edit
End If
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.
Hello,
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
Hi,
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??
Thanks
Hi all,
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...
any ideas?
ta
John