Forms :: Setting Combobox Rowsource To Be A Recordset Instead Of Query?
Jul 15, 2015
I have a Product form that shows the details of each of the Products in my Product table. I also have a "Pick a product" combobox (who's rowsource is being fed by the same Products Query that feeds the form). This allows you to go straight to a particular product by selecting its name. So far, so simple.
I've since added a couple of combobox filters which use different columns ("Product Type" and "Collection") to allow the user to get down to a more manageable recordset to then work with.
Of course, because the "Pick a Product" combobox is being fed by the Products Query, the combobox can end up showing products that are not contained in a filtered recordset. This is... sub-optimal.
Is there a way of telling the "Pick a Product" combobox to use the products listed in the recordset as its rowsource rather than the product query? So that it only lists the products the user has filtered down to?
View Replies
ADVERTISEMENT
Jan 18, 2014
I am trying to design an unbound combobox whose rowsource can be dynamically set based on a union query in order to search 1000's of names returning each name that contains a user defined string. The search does not start until the user defined string reaches 3 characters in length.
There are more details in the attachment but basically the vba code I used all saves OK in the vba editor but I get a runtime error 13 type mismatch when the code attempts to utilise the query to populate the rowsource of the combobox.
View 10 Replies
View Related
May 18, 2005
Hi all,
I have a combo box which gets it's values from a table. It also has a where statement to filter the data given the value in another combo box. I.e.
SELECT DISTINCT Table1.some_field FROM Table1 WHERE Table1.some_other_field = Forms!frmSomeForm.Combo_box
The first time I change the value in Combo_box combo box it will filter the values in my other combo box. But when I change it successive times it doesn't refresh the query, so the same values stay. Any suggestions?
View 8 Replies
View Related
Apr 14, 2014
So I have a combobox in a form that lists the values of a field in a table. The rowsource is this:
Code:
SELECT [Estes Training Modules].[Training Module], [Estes Training Modules].[Period] FROM [Estes Training Modules] ORDER BY [Period];
The problem is that ALL values on that field show up, including repeated values. I only need each different value to show up once. How can I do that?
For example, what I get in the combobox is: Annual, 2-Year, Annual, Annual, 2-Year, 3-Year,...
What I need is: Annual, 2-Year, 3-Year,...
View 10 Replies
View Related
Mar 21, 2015
I have form containing 8 comboboxes. On click on command button i want to set the item/caption/selection of combobox. How should I do that? Button has OnClick event built in VBA. What would be the code?
View 3 Replies
View Related
Dec 4, 2012
I've go a subform with 2 comboboxes. It's a datasheet view.
The combobox controls are called cmb1 and cmb2 for the sake of this...
cmb2 is dependent on cmb1. So when you select a certain value in cmb1, cmb2.rowsource should get updated to use a certain table.
But the kicker is that this should be on a per row basis. So if on row 1 of the subform datasheet you set
cmb1=Manager, cmb2.RowSource should get set to 'tblManagers'.
Then on the next row, cmb1 is set to Directors, cmb2.RowSource should get set to tblDirectors.
But the cmb2.rowsource on row 1 is still set to tblManagers.
View 2 Replies
View Related
Jan 17, 2014
I have a combo box which I define a SQL statement as its rowsource.
When I open the form, I have yet to define the SQL statement for the combo box.
My code is:
if me.cmbobox.listcount = 0 then
me.cmbobox.enabled = false
else
me.cmbobox.enabled = true
end if
But it shows the box as still enabled.
When I do debug.print me.cmbobox.listcount, it shows a value of 1. But that is impossible since I have yet to define a rowsource for it or its empty.
I also have column heading turned off too.
View 3 Replies
View Related
Nov 15, 2014
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]....
View 3 Replies
View Related
Sep 1, 2005
I need to be able to use a combo box on a form to filter the records shown. The fields on the table are as follows:
ID No, Ref No, Start Date, Comp date, Price etc.
I have set the form up to run off a query that shows all records with a certain ID No. Each ID No has a number of reference numbers. I would like to use a combo box on the form so the user can select a Ref No and the form would be filtered to show only the details of that specific ref no.
What is the easiest way to do this?
Any help would be greatly appreciated.
View 4 Replies
View Related
Apr 8, 2015
How to open a Record Set For the combo-box? My Original Code as follows
Dim conn As New ADODB.Connection
Dim objMyRecordset As New ADODB.Recordset
Dim strSQL As String
Set conn = New ADODB.Connection
[Code] ....
After i use this code nothing come out on my combo-box...
View 2 Replies
View Related
Jan 31, 2014
I would like to be able to clear a combobox selection with code but nothing I have tried is working.
Most recently I have tried this:
Code:
Me.cboCompanies.ListIndex = -1
To no avail. The selection still remains highlighted in the control after the code is executed. I want the control to be blank. Also the control goes from being a combo box to a text box until escape is pressed and it is cleared
I have also tried using a simple Undo and that is not working either.
View 8 Replies
View Related
Aug 16, 2013
I have a basic SELECT DISTINCT query for my CBO rowsource. Is it also possible to add custom values to the rowsource? i.e. I'd like my CBO to look like....
Custom Value
Query Value
Query Value.....
View 4 Replies
View Related
Jul 15, 2014
I'm trying to change a rowsource depending on the value in a form. I want to be able to change the value and update the form accordingly. I can change the query but when I try to add the "where" statement to it, I get nothing.This is what I'm using:
Private Sub RepID_AfterUpdate()
Dim strSQL As String
strSQL = "SELECT DISTINCT [Cust No] & ' ' & [Name] FROM [Address Book] WHERE [Address Book].[Cust No] = '" & [RepID] & "' "
Me.NamesList.RowSource = strSQL
NamesList.Requery
End Sub
View 5 Replies
View Related
Sep 9, 2013
I have a form with several objects:
Projects -> which have Sources -> which have SourceTypes
The underlying DB has tables for Projects, Sources and SourceTypes:
Code:
Table Projects;
Fields:
ProjectID : AutoNumber;
etc, etc
[Code] ....
So, I'm trying to get all the Sources for a given ProjectID, and I'm using the following SQL statement:
Code:
SELECT ID, ProjectID, Sequence, Name from Sources WHERE [Sources].ProjectID= MyProject.ProjectID;
Which actually gets coded (because MyProject.ProjectID is a variable) as:
"SELECT ID, ProjectID, Sequence, Name from Sources WHERE [Sources].ProjectID= " & MyProject.ProjectID & ";"
What actually happens is that Access jumps in and says (via a Message Box) "Give me a value for Source.ProjectID.
I've just done that in the SQL statement!
How do I get a 'real' SELECT to work for the RowSource?
View 5 Replies
View Related
Sep 29, 2005
Ive posted similar in the forms section but it has gone unanswered so I've pulled out the offending query and hope someone here can offer insight into this frustrating problem.
I have a form [Training Record] that has 2 combo boxes.
Box A (course_title_combo) has the titles of the various training courses. It pulls 2 columns from the training_courses table, namely courseID and course_title. Only course_title is shown in the box. The control source of this is set to CourseID as this is the field to be written to the training_record table, showing who attends what course.
Box B (course_date_combo) is MEANT to pull through the dates that each course is on, by pulling through the course dates for each course where courseID (as selected by combo box A) is equal to the courseID in the course_dates table.
Here is the current rowsource query for box B - which is basically showing blank fields...
SELECT tbl_course_dates.course_date, tbl_course_dates.CourseID FROM tbl_course_dates WHERE forms![training record test form]!course_title_combo=tbl_course_dates.CourseID
I cant see what is wrong with the query, but Im guessing it must be wrong as it is the rowsource which determines what is included in combo box B.
Any help would be appreciated no end - im totally stuck now :(
View 8 Replies
View Related
Jul 22, 2007
I have a list box with a query as its row source (screenshot attached).
The query gets data from my Contracts table with information on a Consultant ID, Contract ID, and a Contract Status indicator. This effectively represents each contract with a specified status.
What I'm trying to do is allow the user to select a specific contract from this list box, and for my main form to open and display the actual information on the contract they just selected.
A little background on my main form, the main form consists of a main page followed by several Pages, with a subform in each. Thus, my contract screen is designated as Forms![frmConsultantList]![sbfrmContracts].
So, what I'm trying to do right now is collect multiple fields from the selection the user makes (on the listbox with the query row source). To further clarify, each line has a Consultant ID and a Contract ID. How do I pull each from the user's current selection on the listbox and assign them to new variables? As of now, I am only able to select the first field in the list (Contract ID) and assign it to a variable.
How do I do this, or is there a better way?
I apologize if I'm unclear about my situation, so please feel free to ask any questions. And of course, thank you to all those who spend time reading this.
View 3 Replies
View Related
Sep 18, 2006
Can someone help me out with this.. I have a listbox in a form that the rowsource changes based on search criteria.. The rowsource is a select query... I am trying to do two things:
1. View the query in a datasheet view with the click of a button that I've created..
2. Export the query to excel with a click of a button
I have started to set the query as a string by doing the following...
Dim SEARCHQUERY as String
SEARCHQUERY = LISTBOX.Rowsource
Whats next?
I tried: DoCmd.OpenQuery SEARCHQUERY
but it doesn't work... This command only opens an existing access query..
Any Ideas? I know it should be simple!
View 4 Replies
View Related
Feb 25, 2007
This forum has been so useful to me so far... but having searched through a load of topics for a few hours now I just cannot find a correct method of having the ability to update two tables from a form.
I firstly created a query that selected the nessary fields I wish to update from the two tables.
And its apparent that I need to use RecordSet to insert the information from the form into the query.
I have found a few different ways of doing this - none of which work for me :( HELP!
Method 1
Private Sub Save_Record_Click()
'Save all entered information to tblprocess request and tblBackupRequest
Dim db As DAO.Database
Dim rs As DAO.Recordset
messageusr = MsgBox("Save this infomation?", vbYesNo + vbExclamation, "Warning you are about to Save this information")
If messageusr = vbYes Then
Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT .Server, [Backup Request].Location, [Backup Request].BackupType, [Process General].Type, [Backup Request].[Size(GB)], [Process General].Group, [Process General].[Date required by], [Process General].[Requested by], [Process General].[Date/Time of request], [Process General].Notes FROM [Process General] INNER JOIN [Backup Request] ON [Process General].ProcessID = [Backup Request].ProcessID;")
Me.txtserver = rs!Server
Me.cmblocation = rs!Location
Me.cmbtype = rs!BackupType
Me.cmbtype = rs!Type
Me.cmbsize = rs!Size(GB)
Me.cmbassign = rs!Group
Me.txtrequiredby = rs!Date_required_by
Me.txtrequestedby = rs!Requested_by
Me.txtrequest = rs!Date_Time_Request
Me.txtnotes = rs!Notes
'Clear fields on form to indicate write has occurred
txtserver = ""
cmblocation = ""
cmbtype = ""
cmbsize = ""
cmbassign = ""
txtrequiredby = ""
txtrequestedby = ""
txtrequest = ""
txtnotes = ""
'Close recordset and database
rs.Close
db.Close
MsgBox "This information has been succesfully saved"
End 'return user back to form
End If
End Sub
[B]Method 2
Private Sub Save_Record_Click()
'Save all entered information to tblprocess request and tblBackupRequest
Dim db As DAO.Database
Dim sqlStatement As String
Dim saverecord As DAO.Recordset
messageusr = MsgBox("Save this infomation?", vbYesNo + vbExclamation, "Warning you are about to Save this information")
If messageusr = vbYes Then
sqlStatement = "SELECT .Server, [Backup Request].Location, [Backup Request].BackupType, [Process General].Type, [Backup Request].[Size(GB)], [Process General].Group, [Process General].[Date required by], [Process General].[Requested by], [Process General].[Date/Time of request], [Process General].Notes FROM [Process General] INNER JOIN [Backup Request] ON [Process General].ProcessID = [Backup Request].ProcessID;"
Set db = CurrentDb()
Set saverecord = db.OpenRecordset(sqlStatement)
saverecord.AddNew
saverecord(0) = txtserver
saverecord(1) = cmblocation
saverecord(2) = cmbtype
saverecord(3) = cmbtype
saverecord(4) = cmbsize
saverecord(5) = cmbassign
saverecord(6) = txtrequiredby
saverecord(7) = txtrequestedby
saverecord(8) = txtrequest
saverecord(9) = txtnotes
saverecord.Update 'Write new record to database
'Clear fields on form to indicate write has occurred
txtserver = ""
cmblocation = ""
cmbtype = ""
cmbsize = ""
cmbassign = ""
txtrequiredby = ""
txtrequestedby = ""
txtrequest = ""
txtnotes = ""
'Close recordset and database
saverecord.Close
db.Close
MsgBox "This information has been succesfully saved"
End 'return user back to form
End If
End Sub
and I have even looked into an insert sql statement
[B]Method 3
Dim SQL_Text As String
SQL_Text = "INSERT INTO Backup Request (Server, Location, Type , Size(GB)) VALUES ('#Backup Request.txtserver#','#Backup Request.txtserver#','#Backup Request.txtserver#','#Backup Request.txtserver#') &"
INSERT INTO Process General (Group, Date required by, Requested by, Date/Time of request, General_Type, Notes;"
Docmd.RunSQL (SQL_Text, false)
Method 1 seems to be popular but its returning the message
Run time error '3061'
Too few parameters. Expected 3.
:confused:
View 14 Replies
View Related
Apr 26, 2015
I am trying to use a combobox called Manufacturer to select which table the combobox called Model gets it's rowsource from using the code below.
Code:
Private Sub Manufacturer_AfterUpdate()
If (Me.Manufacturer.Value = "Siemens") Then
Me.Model.RowSourceType = "Table/Query"
Me.Model.Recordset = "SeimensTable"
Me.Model.RowSource = "SELECT Model FROM SeimensTable"
Else
If (Me.Manufacturer.Value = "Samsung") Then
Me.Model.RowSourceType = "Table/Query"
Me.Model.Recordset = "SamsungTable"
Me.Model.RowSource = "SELECT Model FROM SamsungTable"
End If
End If
End Sub
But when I run the form and select Manufacturer. Combobox Model remains empty. tell me what I'm doing wrong?
View 5 Replies
View Related
Oct 16, 2014
I'm trying to filter the options in a combo box based on two criteria. The first criterion is another combo box on the same form, for a cascading combo effect. This criterion works fine, no problems at all. The second criterion is a query. So the combo box should display only those values that match both the upstream combobox AND the results of the query. What's really frustrating is that this combobox works just fine in my development dbase. It only stops working when I move it into my active dbase. Anyway, here's the SQL for the combobox rowsource:
SELECT DISTINCT Size2.SizeID, Size2.XYdim
FROM (Shapes INNER JOIN (qrySignsInInventory INNER JOIN Size2 ON qrySignsInInventory.SizeID = Size2.SizeID) ON Shapes.ShapeID = Size2.ShapeID) INNER JOIN Signs ON Shapes.ShapeID = Signs.ShapeID
WHERE (((Size2.SizeID)=[qrySignsInInventory].[SizeID]) AND ((Size2.ShapeID)=(SELECT Signs.ShapeID FROM Signs WHERE Signs.SignID = [Forms]![frmUsedDropdown]![cmbSignID])));
I've hilighted the criterion that isn't working. Also, I'm not getting any errors or anything, it's just that the results aren't being filtered. I've triple-checked the references for the active dbase, and I've checked the results of the query. Everything should be working.
View 4 Replies
View Related
Apr 16, 2014
I have a small form with a combobox that gets its data from a query.
I added code to this database to forward to me info about forms and controls in case they generate errors. Occasionally I get an "error = 0" on this combobox.
Since there are no events attached to this combobox then what could be causing this error?
View 8 Replies
View Related
May 9, 2013
I have a form for data entry that have multiple comboboxes. I am trying to get one combobox to base its "list" on an entry from a previous combobox. The first combobox is based on a QuantityType table and has four options.
The next combobox is based on a ProductDesign table and will have close to fifty options. I want to limit the ProductDesign combobox based on the QuantityType selection which will give the QuantityTypeID. This will refer to the QuantityTypeID linked to the ProductDesign table.I have created a query that looks like this:
SELECT Product_Design.Product
FROM Product_Design, Quantity_Type
WHERE Product_Design.QuantityTypeID = Quantity_Type.QuantityTypeID
AND Quantity_Type.QuantityType = [Forms]![Product]![Qty_Type];
This Query works and if I simply run the query I get the needed information from it.What I would like to do with this query is to populate the ProductDesign combobox with this data.I have tried putting the query in the RowSource field but I get an empty combobox without the data. The strange this is that the combobox must be getting something from the query because the length of the combo box varies based on my QuantityType selection. I.E. if I select "Single" in the QuantityType, the ProductDesign dropdown shows three empty places for data whereas if I select "Multiple" in the QuantityType I see that there is ten empty places for data.getting the combo box to actually show each option?
View 1 Replies
View Related
Oct 22, 2013
My database has comboboxes to autopopulate fields. These comboboxes have parametered queries for row sources so that the user doesn't have to go through a long list of IDs and to make the database more efficient in case it has to hold hundreds of records. They work the first time round but not after, when the comboboxes are clicked for a second time the query parameter is not popping up.
View 1 Replies
View Related
Jun 6, 2013
There is a command button on my form that calls a filtered query. I'm filtering the query based on a combo box within the form using
[Forms]![frmAssignDwgs]![Combo15]
It works beautifully until I put the form as a subform in the Navigation Form I'm using. When I execute it from within the Navigation form, it errors out looking for [Forms]![frmAssignDwgs]![Combo15].
Is there another syntax I can use so this will still work from within the Navigation Form? Or another way to approach this functionality?
frmNavigation is the main navigation form
frmAssignDwgToPkgs is the subform that holds Combo15
I found another thread (which I can't link to since I'm new) and tried various arrangements of the following to no avail.
[Forms]![frmNavigation]![NavigationSubform] .[Forms]![frmAssignDwgToPkgs]![Combo15]
View 3 Replies
View Related
Oct 7, 2005
Hello everyone,
I tried to post this yesterday but it didn't go through for some reasons.
I've attached my database that contains student, classes, and couple other tables. I also created couples of forms and I wanted to register a single student to multiple classes at once.
For example: There're 3 classes (Math, English, History) on 1/3/2006. Student A wants to register 2 classes, Math and English. I should be able to check those two and submit and I'm done.
Currently, I have to select Math and assigns to student A, then select English. That's not good at all. What if there're 10 classes in one day and a student just want to register 8 classes? I have to do eight times.
It'd be great if you could help me. Thank you in advance.
Isabel
View 2 Replies
View Related
Mar 24, 2013
I created a navigation form on which I put a form call [frmAnimal Setup].
I then placed one combo box on the Navigation frm Header. I have bound it to its source and it actually queries the tbl and show the right info. However when I select one, it will not let me.
In addition, [frmanimal setup] will not allow me to select a breed although it does query the tbl and shows the right information.
View 1 Replies
View Related
