I'm working on and Access 2003 front end with a SQL Server Back End. I would like to take advantage of the speed of Stored Procs to populate a list box on a form. Here's the code I'm trying to use.
Set cmd = MakeStoredProc("StoredProcName")
Set prm1 = cmd.CreateParameter("ParamName", adInteger, adParamInput, , Me![ID])
cmd.Parameters.Append prm1
rstSource.CursorLocation = adUseClient
rstSource.CursorType = adOpenKeyset
rstSource.LockType = adLockOptimistic
Set rstSource = cmd.Execute
Me![ListBox].Recordset = rstSource
Access doesn't like this. What is wrong. I get the message "Object doesn't support the property or method"
I have and access form that has a list box with dates in it. I need to pick a date and then run a stored procedure in SQL based on that date. Can someone help me with the login to pass on the date?
So if I have listbox1 which is a list of dates from another table and my procedure is simply
select * from main where date = *******
How can I pass the list box value to the stored procedure? My procedure works with WHERE date = (SELECT(LEFT(GETDATE(),11))) but I have since learned that todays date will not always be used so I need to pass a value.
Im having some Major issues with this problem, i only hope someone can help me!!
Firstly, let me set the scene. I have 1 Form, And 1 Subform within that form. The form gets the Main Data, the SubForm gets all the Related Data (in this case, its all the Items Of Equipment this client has) and then lists them in the subform. So it could return 1, or it could return 20 records.
What we want from this is to be able to record what the client thought of the piece of equipment, so there is a Option Group on the SubForm, which allows the user to choose 1 of 6 options saying whether it was useful or not and things.
BUT here lies the problem, if the SubForm has returned 20 records, i change the OptionGroup on record one, and every other 19 records become the same. if i change Record 2, record 1 and 3 to 19 are the same.
I want to be able to have a seperate choice for each record returned, but tis not working!!
The subform is populated by calling a SQL SP, i cant set the form to link directly to a view or SP because access doesnt allow me, by saying the recordset is not updatable!
I have a DAO recordset(rcdSearch) which contains a number of fields(eg UNIQUE_NUMBER, AREA and SITE_NAME). Assuming the recordset is populated(in this case I know it is), how can I populate a listbox with the data? I have tried the following with no joy
me.listBox.RowSource = "rcdSearch" 'puts string "rcdSearch" in column header me.listBox.RowSource = rcdSearch ' returns type mismatch me.listBox.RowSource = rcdSearch!UNIQUE_NUMBER ''puts unique number in column header row me.listBox.Column(0) = rcdSearch!UNIQUE_NUMBER ' object required error msg
I have even tried using the SQL string that generates the Recordset, this does not return any results. :confused:
I would be grateful for any help on this issue, as it has been doing my head in for days!
How does one go about removing a recordset from a ListBox?
I have a list box that I want to toggle between using a query and an ADO RecordSet to populate the values.
Once I set the listbox .RecordSet property to the ADO.Recordset, I can't remove the values displayed in the listbox when I assign a query to the .RowSource property.
I suppose I can turn the .RowSource query to an ADO Query but I am being lazy and don't want to rewrite the query as a T-SQL query.
I previously thought the list box was pulling data from the .RowSource query but I realize I was wrong.
I'm trying to populate a listbox from a recordset. I will explain what I'm trying to achieve.
I have a (continuous) form with some data from a table. On the form header I have a listbox, showing all (distinct) customers from that form.
The user is able to do some filtering. That all works great. But I want to populate the listbox with ONLY the customers on the form AFTER filtering.
One way I thought might me the solution was using RecordClone. But I'm stuck there. It must be something simple for an expert. I'm not entirely a newbie, at least not to VBA. Is there an easy way to refer the listbox' recordset to the actual recordset on the form?
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?
Hi, I'm new here, so I hope I'm posting this in the correct place. I've searched the forum to see if there are any existing threads that might help me, but I've not found anything that does... (I think this thread ( http://www.access-programmers.co.uk/forums/showthread.php?t=93444&highlight=Copying+data )may be trying to achieve something similar to me, but I'm a beginner and don't really understand it)
I shall stop waffling! I'm not entirely sure that what I'm trying to achieve is possible, I expect it probably is!
Right, I have a form (frmGroupRegister, which contains exactly the same fields as the table it comes from, tblGroupRegister), which consists of three things:
-GroupDate - The date a group took place on. It is my primary key, as no more than one group occurs on a specific date.
-ParentList (A listbox which contains a query showing the ID number, forename and surname of everyone in a table, tblParentDetails)
-ParentsAttending (A blank listbox)
I would like to place buttons in between the ParentList and ParentsAttending, which would allow users to conduct a 'register' of attendance by copying individual/multiple details from ParentList into ParentsAttending (much like you get when choosing which fields to include in a form when using a wizard for example). I would also like them to be able to remove people from ParentsAttending by using a button in case of accidentally adding the wrong person into the ParentsAttending box.
I'm aware that another, probably simpler way of achieving this would be to use a tick-box system, but I feel that visually, the first method would both look better and demonstrate who is present more clearly.
Any help would be much appreciated, but my Access skills are quite basic and things will probably need to be spelled out for me. I'm using Access 2000 and Windows XP. Thanks for your help, Alice :)
I am using the selections made of the form to generate a query for the user.
I have a CITIES listbox that is populated with values from a stored query.
I would like to make it multi-select and populate a LOCATIONS list box and a NAMES list box based upon the CITIES that are selected.
I have the locations currently populated from a stored query that reads the City selection from the Form. It looks like this
Code:
SELECT DISTINCT (t_location.LOCATION) AS Expr1 FROM t_location INNER JOIN t_asset_master ON t_location.LOCATION_PHY_ID = t_asset_master.LOCATION WHERE (((t_location.CITY)=[Forms]![MasterQueryGenerator]![CityList]));
I also want multi-select so that is you can un-select all and get the results for all cities.
Here is my half thought approach.
Code:
Private Sub CityList_AfterUpdate() 'Dim LocQryStr As String 'Dim r As Integer 'Dim ctl9 As Control 'LocQryStr = "SELECT DISTINCT (t_location.LOCATION) " & _
[Code] ...
I intended to have the variable LocQryStr as the row source but I abandoned the idea of having multi-select when I saw that .Selected(I) never returned true. Its like the values aren't read in this subroutine.
I have a list box called "product list box" based on a query called "searchqry", i also have another listbox called "type list box" , how do i get the type list box to only show "types" based on the section in products list box?
1. On my main form, I have a listbox, I would like to edit the values of the listbox.
To do this, I have a popup form with 2 listboxes, one to have the values of the listbox on the main form, and the other listbox with option values for the 1st
1) how to i pass the rowsource sql of the listbox on the main form to the listbox on the popup form
2) how on closing the popup form, do i update the rowsource sql listbox on the main form from the changed value of the popup form listbox rowsource sql
Okay then, after much trouble and confusion, I finally realized I need to use an Extended listbox in order to allow for multiple items to be selected from a list on my form (rather than the evil multiple selection combobox!).
However, now I am trying to figure out how to make one listbox (IndustryClassification) only be visible if the item "Industry" is selected in another listbox (TypeOfBusiness). Coding I can use for this in the AfterUpdate event of the listbox?
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
Hi, I need to write a stored procedure that sits between sql server and MS access. This procedure will make sure that the queries run in access against the data in sql server will not access data for more than two years back. This is so that the queries run against the sql server do not hang up the server for too long.
I've read the posts regarding not store calculations. However, I was told by IS to store the mileage IRS deduction as it changed 3 times last year.
Any idea's how to handle this? I need the table to remember how much $$ was paid out in mileage expense. I had set up a field called mexpense and mileage, where =[Mileage]*0.375.
I apologize that I am not a very good VBA programmer yet but I'm really trying....
I am attempting to calculate a value by adding two fields and storing the result into a third field - - My code only works if both of the fields are not null.
Private Sub PartialDepositAmount_AfterUpdate()
Dim PartialDepositAmount As Currency Dim FinalDepositAmount As Currency Dim DepositAmount As Currency
I've got this multiple select listbox which writes data into a textbox:
Private Sub List2_AfterUpdate()
Dim Cursisten As String Dim ctl As Control Dim Itm As Variant
Set ctl = Me.List2
For Each Itm In ctl.ItemsSelected If Len(Cursisten) = 0 Then Cursisten = ctl.ItemData(Itm) Else Cursisten = Cursisten & "," & ctl.ItemData(Itm) End If Next Itm Me.txtCursisten = Cursisten
End Sub
And I've got a SELECT ALL button to select all records in the listbox:
Private Sub cmdSelectAll_Click() On Error GoTo Err_cmdSelectAll_Click
Dim i As Integer
If cmdSelectAll.Caption = "Alles Selecteren" Then For i = 0 To Me.List2.ListCount Me.List2.Selected(i) = True Next i cmdSelectAll.Caption = "Alles De-Selecteren" Else For i = 0 To Me.List2.ListCount Me.List2.Selected(i) = False Next i cmdSelectAll.Caption = "Alles Selecteren"
The only thing is that when I use the SELECT ALL button, the function List2_Afterupdate doesn't work anymore. There must be a simple solution but I just can't figure it out. Can anyone please help me?
Hopefully somebody can help me on this one. I searched the whole internet and access forums, but I didn't find the exact solution for my problem.
I've got a table with students, a table attendance, where I now only save the students who are absent, but I would like to save also the students who are PRESENT (at the same time). I've got a combobox where I filter the Class, which then updates a listbox with the students from that class. What I do now is select the students from the listbox and then press a save button and it saves the records to the table absence with STATUS: ABSENT.
I would like to save the NON selected students also in that table, but with PRESENT in the column STATUS.
I thought of making another listbox next to it, where after selecting the absent students, they wil apear and disappear in the PRESENT table so I can store all the information. But the only problem is that I can find this solution when the listbox is populated by a list of values instead by a table or query. And the other solution is to store the temporary data into 2 different tables, but that's not working for me because it's a multi user database and everything will be messed up.
Hope that someone can help me, I will be very happy.
