not sure if this is the correct forum but here goes. is it possible to have a disconnected ADO recordset populate a combo box in an Access Project (.adp)? i have a Access XP front end piping back to a SQL Server 2k dbase. i'm using a stored procedure to handle my insert, however i have a couple of combo boxes on my data entry form that i need to populate on the form's "OnLoad" event. i was wondering if i can populate these using a disconnected recordset (similar to functionality of the dataset object in ADO.NET)?
if not any ideas of best practices to populate combo boxes and limit connection resources?
just curious if it was possible to populate an empty tables' fields from a recordset that had been defined earlier in VBA under the criteria of a bit of sql code?
I am trying to populate a recordset in Access97 using the following code, but cannot seem to get it to work.
The table name is "EventData" that has Type and date fields. startdate and enddate are text boxes on a Form, for wich the user enters the date range, and then clicks a command button to build an Excel spreadsheet on the fly that will be populated with the results of the recordseet.
here is the code:
Dim db As DAO.Database Dim rst As DAO.Recordset
strSql = " SELECT EventData.EventType, Count([EventData].[EventType]) AS [Num Occurrences] FROM EventData WHERE ((EventData.Date) >= (Me!startdate)) GROUP BY EventData.EventType"
The error message that I am getting is "run time error 3061", too few paramaters. ALSO not sure if I need a semicolon right before the closing quote of strSql.
So Im trying to populate the listbox using string connection and having this error: "the object you entered is not a valid recordset property"
here's the code: Dim cnn As ADODB.Connection Dim rst As ADODB.Recordset Set cnn = New ADODB.Connection cnn = "DRIVER={MySQL ODBC 5.1 Driver};SERVER=xxx;DATABASE=xxx;Port=xxx;USER=xxx; PASSWORD=xx;OPTION=3;"
[code]....
Where is the error??? am doing right or is there any better way to populate listbox from string connection.
I have several comboboxes (6) on my form.How to populate these comboboxes with values depending on selected value in previous combobox.
Example.Lets say that you select value "Audi" in combobox 1, then available values in combobox 2 should be "A4","A6","TT" etc. and if you selected "BMW" in combobox 1, then available values in combobox 2 should be "3-series", "5-series" etc...
I'm have quite a difficult time getting a form in Access 2010 to perform the way I would like it to. I have multiple tables that I've created, and a query that contains the data from the various tables. I then ran the form wizard to select the data from the query that I wanted to populate in my form and I've added 2 combo boxes.
What I want to do: 1. I want users to be able to select a category in combo box #1 (example: "Bag") 2. I want users to be able to select a detail in combo box #2 based on the category they selected in combo box #1 (example: Combo box #1, "bag" would populate the following selections for combo box #2: "sandwich" and "tool") 3. I want users to then receive a list of suppliers that provide the product they have selected, either "Bag: Sandwich" or "Bag: Tool"
I have combo box #1 populating a list of categories already. However, I am not able to get combo box #2 to provide choices based on the selection chosen for combo box #1.
I am a self-admitted beginner so go easy . I have a query populated with all of my data. I am building a form based on two columns in the query: Pick Up Name, and Deliver to Name. Lets say I have records for company XYZ picking up at location A three seperate times, location B once, and location C four times. On the form I want to have my first combo box listing all the names of column "Pick Up Name". When I scroll down and select company XYZ, I want my second combo box to only display the three locations where company XYZ has picked up: A, B and C. Then I want the option of selecting either A, B or C and in a third box, display total number of times that company XYZ has picked up at selected location.
For example, I select company XYZ from the first drop-down. I select C from the second drop-down, from the list of three locations. I get a result of four, because company XYZ has delivered to location C four times.
Right now, I have my first combo box with the Pick Up Name data, where do I go from here? Any help or nudges in the right direction greatly appreciated!
I want to update a field (field1) in each record of a recordset (qryUpdate)
I got the following code (in green) working fine - so far so good. I actually need to update the field from a selection from an unbound combo box. This looks up a table with two fields Period ID (autonumber PK) and a text field called Quarter. I've tried various combinations including the code in red and code such as "rst![field1] = Me.cboPeriod" "rst![field1] = Me.cboPeriod.Column(0).value" etc
but can't get it to work. It has to be something simple but I can't see it.
Private Sub cboPeriod_AfterUpdate() Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("qryUpdate") Do Until rst.EOF
If IsNull(rst![field1]) Then rst.Edit rst![field1] = 5 rst.Update End If
rst.MoveNext Loop End Sub
Private Sub cboPeriod_AfterUpdate() Dim rst As DAO.Recordset Set rst = CurrentDb.OpenRecordset("qryUpdate")
Do Until rst.EOF
If IsNull(rst![field1]) Then rst.Edit [rst![field1] = Me.cboPeriod.Column(0) rst.Update End If
Hi There. Im Trying To Filter Records Through A Combo Box Which Is So Far So Good. However What I Would Like To Do Is Select The Records Through The Combo Box And Have It Return And Populate Only Those Records. Has Anyone Got Any Suggestions To This? Ive Attached The Sample Database Im Working On.
Hello Everyone, I am stuck in a problem for many days now. In the form, on which I am working, there are 2 dropdown lists (combo box). The data in the first combo is coming from a list and in the second combo, I am running a query, and depending upon the selection in the first combo, the data in the second combo may come from one of many tables in the database. What I want is to have control over the data, before it is being displayed in the second combo, as the data in have many null and 'same name' entries. But what I don't want is to change the original data in the tables.
Till now, I have been trying to make use of persistant recordset, that is, by storing the recordset in an external table, changing its data and binding it to the second combo. But I am unable to bind the data.
What in your opinion, is the best approach to accomplish this. A snippet will be of great help as I am not a experienced programmer.
I have a table called Contacts and a form with two combo boxes for searching for records either by name or property name. The following code works fine for finding the first record, but I want to be able to show on the form all (and only) those records which match the combo box entry. Currently rowsource for name box is: ************************************************** * SELECT [Last Name1] FROM Contacts UNION SELECT [Last Name2] FROM Contacts ORDER BY Contacts.[Last Name1]; ************************************************** * and code is: ************************************************** * Private Sub Combo214_AfterUpdate() ' Find the record that matches the control for Last Name search Dim rs As DAO.Recordset
Set rs = Me.Recordset.Clone rs.FindFirst "[Last Name1]= '" & Me.[Combo214] & "' OR [Last Name2]= '" & Me.[Combo214] & "'" rs.FindNext "[Last Name1]= '" & Me.[Combo214] & "' OR [Last Name2]= '" & Me.[Combo214] & "'" If Not rs.EOF Then Me.Bookmark = rs.Bookmark Combo214.Value = "" txtFirstName1.SetFocus End Sub ************************************************** * Rowsource for property box is: ************************************************** * SELECT Contacts.PropertyID, Contacts.PropertyName FROM Contacts ORDER BY Contacts.PropertyName; ************************************************** * and code is: ************************************************** * Private Sub Combo212_AfterUpdate() ' Find the record that matches the control for Property Name search Dim rs As dao.Recordset
Set rs = Me.Recordset.Clone rs.FindFirst "[PropertyID] = " & Str(Nz(Me![Combo212], 0)) If Not rs.EOF Then Me.Bookmark = rs.Bookmark Combo212.Value = "" cboPropertyName.SetFocus End Sub ************************************************** **
I have done this before but cant remember how I did it...
I have a text box for Street Number
After the user types in a number (On Change) or when the StreetName (On Enter) I want the StreetName Combo Box to narrow down the street choices based on the Street Number.
So I need a query that will look at the Street Number textbox and then run a query to select jsut the Street Names with that House Number.
I cant remember the query or what code is needed to requery the Street Name combo box everytime it is clicked.
I accidentally posted this in "general"...so I am posting it here under "forms", since it is "form" related. My intention was not to double-post...sorry. Summerwind had posted an example to auto populate a combo box. The code below follows his example, but is not working for me:
The fields I have on my form are:
cboCompany cboLocation
cboCompany is based on tblCompany( with fields CompanyName, Location)
When I update the cboCompany field, I want the cboLocation combo box to populate with all locations for the company that was entered into the cboCompany combo box on the form. E.g. The company "Visions" may be exist in the tblCompany with 3 different locations: Visions (Las Vegas), Visions (Ohio), Visions(California). When I pick the company "Visions" from the cboCompany combo box, I want the cboLocation combobox to be populated with "Las Vegas", "Ohio", and "California".
The code is as follows but returns empty records in cboLocation:
Code: Dim StrSource As String
StrSource = "SELECT Location FROM tblCompany WHERE tblCompany.CompanyName Like " & Me.cboCompany.Column(1) With Me.cboLocation .RowSource = StrSource .SetFocus .Dropdown End With
I have a combo box on a form that shows 3 columbs from tblparts in the drop down, I wish to select one row from the drop down, that will enter the selected data in three fields on the form.
For example my form has three fields "part code", "Description", "cost".
Part Code = combo box Description = Text Box Cost = Text box
I want to select the data from the combo drop down to populate all three boxes.
Can sombody please help, as I have been stuck on this all afternoon. Cheers
I'm creating a form that will have a combo box and several other fields on it. From the combo box, I want to be able to select a line from the combo box, click on the line and then have the form's other fields get populated.
I know I have to have the combo box and fields tied to either a table or to an underlying query, but what might be my best way to get the form populated?
Should I, in code tied to the form, run some coded SQL based on what I select and use the SQL results, or should I use something like recordsetclone, of which I have no experince?
Thanks inadvance to thoe who respond!! I reallyappreciate your help!!
Hi, I want that when a value from combo box is selected, based on it the values in the text box should appear. These are all bound columns.
I have written the following code and getting as error "Run time Error 3075" syntax error "missing operator" in query operation at line - >Set rs = db.OpenRecordset(strSQL) in the following code appears. Please help.
Private Sub Assigned_To_LostFocus() Dim db As DAO.Database Dim rs As DAO.Recordset Dim strSQL As String
Assigned_To.SetFocus If Assigned_To.Value > 0 Then strSQL = "SELECT * FROM Department WHERE Assigned To = " & Assigned_To.Value
Set db = CurrentDb Set rs = db.OpenRecordset(strSQL)
If Not rs.BOF Then Me.Text65 = rs.Fields(1) / Me.Text65 = rs("code") Me.Department = rs.Fields(2) / Me.Department = rs("Department") End If rs.Close Set rs = Nothing db.Close Set db = Nothing End If End Sub
This is what I have. What I need is to populate in my form PartNo with the PartNo from tblfbompart and have it populate PartDesc from tblbompart and have it place the information into tblMadeToStockParts PartNo, & PartDesc. Also choose Model from tblModel using a combo box and placing ModelID in tblMadeToStockParts.
tblMadeToStockParts MTSID PartNo PartDesc Model Memo Attachment
tblfbompart fbompartID PartNo PartDesc Category
frmMadeToStockParts MTSID PartNo PartDesc Model Memo Attachment fbompartID ModelID
I am creating an inventory database and was wanting to know how to make a combo box or any box auto populate information from tables. I would like to have a combo box so that you can do a search inside of it and find the necessary product, select it, and all the product specs will be displayed. I have created the combo box with this information, but no luck getting it to populate on the form itself. Thanks in advance for your help.
I'm doing a course on Access and I'm trying to populate a text box based on the value I select in a combo box.
Basically, in the combo box I want to select a drug (for pets, it's for a veterinary clinic...) and in the text box next to it I want the cost to pop up. The list of drugs with their costs are listed in a separate table, with the drug name in column 1 (counting from 0) and the cost in column 3. I then have a continous form so I'd like to be able to keep adding drugs and their costs and then sum up the drug costs in a text box with the total.
I have 2 questions which are linked. I am trying to run a query based on a selection from a combo box. Would someone be so kind to indicte if i need to use VB or can i do it directly from Access?
I have made the query and the combo box table but i am unable to get the combo box selection to be used in the query.
Additionally i want the query to look at a date range. In the query criteria i use "=now()" but i want the combo box selection to populate the rest of the criteria so it look like =now()+ XXX where XXX = a number of days, eg 14 or 30.
Trying to update fields (bound) on a form (built using the wizard) from value selected in a combo box. I can get this to work with unbound text boxes using an After Update event handler and a code query along the lines of:
Me!TxtJobCode1 = Me!CboJobDesc.Column(1)
But this doesn't work where the form field is bound to a table.
So qu is - how do I achieve the same result using table fields instead of text boxes?
I could use text boxes but not sure then how I can save that data as a record in the table if it is not bound to fields.
I am newish to background coding in access 2010. I have a call log with a form which is where the information on the caller/area is entered. There's a combo box that needs to populate a textbox with 3 different things, depending on whats selected. I'm using If,Then, Else.
The first selection needs to just have the textbox say something. I have that figured out with:
If Me.CboSource = "..." Then Me.Title = "..." (cboSource = combo, of course, and Title is the textbox.)
Next is where I'm fuzzy....The second selection needs to have the textbox populate with a dropdown list of choices from another table. No matter what I do, nothing is working. My latest attempt is:
ElseIf Me.CboSource = "External" Then Me.Title = [tablename].[name]
So I've created 3 tables - one for each information group arranged like so. However my problems are - I don't know how to make it so that as soon as all the info for one set of parts is filled another set automatically appears? And in the Design section of the Form I have the following error because I wasn't too sure what to use apart from a text box-
