Using VBA to control combo-box rowsource - Trouble


Hello,

I am getting rather fusrtated - I have built a form including 2 comboboxes. the frist combobox uses an SQL query to select rowsource from my master table fine - however the second combobox recordset will vary depending what is entered in the first combobox. I have written a marco set in the 'on exit' event from the first combobox it goes like this;


	Code:
	Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Dim mySql As String
mySql = "SELECT DISTINCT [Vessel In Date] FROM [Master Table - Packages] WHERE ([Master Table - Packages].[Vessel Name] =" & Me.Vessel_Name & ")"
Set rs = db.OpenRecordset(mySql)
Me.Vessel_In_Date.Recordset = rs

the problem is - i keep getting an error using this - why would that be?

Any help would be greatly appreciated
Thanks
P


Sponsored Links:



Hi
I would like to use a combo box in a form, but would like to control the contents using VBA code. Im not sure how to get started - can anyone give me an example of how to populate a combo box from another table in the database? (I know that this can be done using queries - but I need more control that this over the list in the combo box)

Thanks for in advance for your help
Mike Wood




Hi

I'm new to access. I've got a form [Performance Dynamic Report Builder] that has 3 combo boxes on it [specialty], [gender] and [grade]. I would like to be able to filter my report [Dynamic Specialty Performance] using up to 3 combo boxes. Sometimes only 1 box will be used so the other 2 will be blank, sometimes 2 will be used etc.

Thank




Oh help!

I'm trying to add the option of choosing "all" to my combo box. My row source is pulling the data correctly from my table and the form works wonderfully. But then when I try to add in my code to add an "All" function, it stops working. Any ideas?

Here's the code I'm using. It came from msdn.microsoft.com/en-us/libr....12).aspx#Y515:

Function AddAllToList(ctl As Control, lngID As Long, _
lngRow As Long, lngCol As Long, _
intCode As Integer) As Variant
Static dbs As Database, rst As Recordset
Static lngDisplayID As Long
Static intDisplayCol As Integer
Static strDisplayText As String
Dim intSemiColon As Integer
On Error GoTo Err_AddAllToList
Select Case intCode
Case acLBInitialize
' See if function is already in use.
If lngDisplayID 0 Then
MsgBox "AddAllToList is already in use!"
AddAllToList = False
Exit Function
End If
' Parse the display column and display text
' from the Tag property.
intDisplayCol = 1
strDisplayText = "(All)"
If Not IsNull(ctl.Tag) And (ctl.Tag "") Then
intSemiColon = InStr(ctl.Tag, ";")
If intSemiColon = 0 Then
intDisplayCol = Val(ctl.Tag)
Else
intDisplayCol = _
Val(Left(ctl.Tag, intSemiColon - 1))
strDisplayText = Mid(ctl.Tag, intSemiColon + 1)
End If
End If
' Open the recordset defined in the
' RowSource property.
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(ctl.RowSource, _
dbOpenSnapshot)
' Record and return the lngID for this
' function.
lngDisplayID = Timer
AddAllToList = lngDisplayID
Case acLBOpen
AddAllToList = lngDisplayID
Case acLBGetRowCount
' Return number of rows in recordset.
On Error Resume Next
rst.MoveLast
AddAllToList = rst.RecordCount + 1
Case acLBGetColumnCount
' Return number of fields (columns) in recordset.
AddAllToList = rst.Fields.Count
Case acLBGetColumnWidth
AddAllToList = -1
Case acLBGetValue
If lngRow = 0 Then
If lngCol = 0 Or lngCol = intDisplayCol Then
AddAllToList = strDisplayText
End If
Else
rst.MoveFirst
rst.Move lngRow - 1
AddAllToList = rst(lngCol)
End If
Case acLBEnd
lngDisplayID = 0
rst.Close
End Select
Bye_AddAllToList:
Exit Function
Err_AddAllToList:
MsgBox Err.Description, vbOKOnly + vbCritical, "AddAllToList"
AddAllToList = False
Resume Bye_AddAllToList
End Function

I then change my row source type to "AddAllToList".

And then the combo box quits working.

Any suggestions would be appreciated!!!




I need to fill a combo box using a SQL statement from VBA.

I have written a module that searches 2 recordsets and compiles a SQL statement based on employees that are either already scheduled to work or have asked off. An example...

SELECT Name FROM tblEmployees WHERE Name"BOB" And Name"MARY" And Name"TOM" And Name"STEVE" And Name"MIKE"

I want the combo box to contain the results of this query, ie: only the names of the employees that are available to work on a particular date. This query gets the correct data but how can I put the results into a combo box?

I can't use a Make Table query because we have a networked system that feeds off of one computer, and 2 employees can be looking at 2 different dates simultaneously.


Thanks




i have a question

i want to use a text box to controp the content of the combo box.
for example

table [key]
id key
1 xxx1
1 xxx2
1 xxx3
2 yyy1
2 yyy2

there is a text box called "id" and combo box "key" on a form
when i fill the [id] text box , ie "1", then the combo box will only show "xxx1,xxx2,xxx3" !!

i build an event (after update event) on the text box and
i don't know how to set the row source of combo box.

can anyone help?
thank you very much !




What wording in VBa is used to move on exit from a text box in question one to a combo box in question 2 on my form? Question 1 textbox is named txtAC01; Question 2 combo box is named numAC02. I have tried several combinations of words "move to," "jump to" however, I get an error message. Thanks




I have a 2000 database running with Access 2007. I have several forms that use a number of combo boxes. All the combo boxes had a rowsource of a table in error (for example a customer table). They should have had the field selected. For example, the CustName control should list the names from the customer file. The CustState should list the states, etc. Instead every dropdown listed all the customer numbers since that was the first column in the table.

I zipped the database and transferred it from the client site to my home/office computer. I modified the forms to correct the combo boxes to list only the appropriate columns from the Customer table. Tested and worked fine. Then I zipped it and sent it back to the client's computer.

When I tested it on the client's computer, I got some very strange results.

1. If I test any combo box dropdown, nothing changed... it still listed just the customer numbers.
2. I open the form in design mode, changed something insignificant, saved it... no change.
3. I open the form in design mode, edit the rowsource for some of the controls, one at a time, to open the query design window, DIDN'T CHANGE ANYTHING, JUST SAVED IT AS IS, saved the form and WALLAH it worked for those controls. The other controls still didn't change.
4. I had to do this for every combo box control to make it work.

Any ideas why I had to go through these steps and how can I correct it?

Thanks, Eddie




Hey,

I've been searching the internet for days trying to work out how to control (screen scrape) RUMBA mainframe using VBA. I have found a few hints and tips but nobody seems to have posted any sample code that I can interrogate and adapt.

Does anybody here know how to automate RUMBA using VBA? Main functions I need are:

- Test fields in RUMBA for certain data
- Capture information from RUMBA and use it within the VBA code
- Paste information from VBA into RUMBA

If anybody could help, this would be great!

Oliver




Hi,

I have a form on which I have a combo box. I would like to load the combo box with the values from a column in a particular table, allow the the user to select records with the combo box, and enter new values for new records/update values for existing records in the combo box.

The trouble that I am having is that, when changing records with the built in Access control thing at the bottom of the screen ( Record: |< < __1__ > >| >* of X ), the selected item in the combo box does not change to match the current record. So, I am searching endlessly for documentation of this control and all I can find is a help page called "Combo Box Control Properties" which doesn't mention anything that would allow me to make a certain item in the combo box selected and I don't see any documentation of methods.

A) Is this possible?: to programatically (with VB) make a particular item in the combo box selected.

B) How do I do it/where is the appropriate documentation?

Any help or insight appreciated. Thanks.

[This message has been edited by JMM (edited 04-16-2002).]




Hi,

I hope this is the correct Forum!

I have a Commitment Form and one of the combo boxes the user is to fill in is ExpSubjective.

There is a button on this form which then opens the Income Form, (currently it pulls the commitment ID over which is fine) and the user has to select the IncSubjective from a list within the IncSubjective Combo Box (within the properties of this combo box, there is a query that limits the available income subjectives based on what the expenditure subjective was in the previous form).

What i would like is to have the Income Subjective automatically populated in the form when I open the Income Form rather than the user having to select it from a drop down list.

My tables are set up as follows:
Expenditure Subjective
ExpSubjective (1)
Expenditure Description

Income Subjective Table
IncSubjective
IncSubjectiveDescription
ExpSubjective (to Many)

The issue is A) I don't know how to write this in VBA and B) there will always be the possibility of there being two income subjectives to every expenditure subjective, the second will always be a specific code say 92243 (although I always with it to select the other code not 92243).

Can anyone help please.




Am trying to use VBA to dynamically build a SQL statement that will end up being the data source for another form. (want to enable my users to enter in criteria as they see fit on a search form, collect their criteria and then build the statement programatically in SQL).

I'm so close I can taste it but I must be missing something simple.

I want my SQL SELECT statement to include reference to a combo box called cboClient but can't seem to figure out the best way to include cboClient in my SELECT statement. I 'believe' my snag has to do with my syntax (doesn't it always) in terms of single quotes, double quotes, brackets, etc.

Would appreciate any help ....

Mitch




I am relatively new at working woth forms for user updates. I am trying to create a form from a database so that multiple users can select a record from the database using a combo box, and update the data in the underlying tables. I am having difficulty with two issues.

1. I want to set up my form so that the user cannot accidentally change data in a record and have it automatically saved. Instead I would like to add a prompt so that if the user makes changes and attempts to edit the record, they get a message asking "Are you sure you want to update this yes or no?"

2. I also want to disable to mousewheel. I have had great difficulty creating a basic form that could accomplish these two things.

I have been advised to "use VBA to edit the data" but as mentioned I am a newbie at form creation. Any help would be appreciated, or if there is a click by click tutorial that demonstrates how to create a form in this manner. Thanks in advance.




Dear Access Expert

I want to add an item to a combo box value list.

The code below works great. However, once the form is closed the newly created value list is replaced by the original (old/ former) value list.

I want the new value list that the user defined to remain intact even after the form is closed.

In the code I tried saving the form using Docmd.save but no Luck.

Thanks so much for your help.


	Code:
	Private Sub CHM_R1_NotInList(NewData As String, Response As Integer)
      
      Dim intResponse As Integer
      
      intResponse = MsgBox("The Chemical '" & NewData & "' is not in the List.  Would you like to add '" & NewData & "' ? ",
vbQuestion + vbYesNo, "Aimco Solrec")
      
      If intResponse = vbYes Then
            Me.CHM_R1.AddItem NewData
            Me.CHM_R1.RowSource = Me.CHM_R1.RowSource
            Response = acDataErrAdded
            Docmd.Save
      
Else
      
            Response = acDataErrContinue
      End If

End Sub





I'm using Access 2003 and on an existing form I'm trying to convet a text box to a combo box.

I used the "Convert To" function by right clicking on the text box and selecting combo box. The control changed and looks fine.

I left the Control Source as it was and created a SQL in the Row Source with only one field. The SQL works fine and displays the list of values I want the combo box to display.

The bound column is 1, pointing to the only field in the SQL. The number of columns property is set to 1, as well. The Column Width property is set to the same with as the combo box.

Now for the part that's making be crazy. When I put the form into Form View the value from the Table displays, but if I open the combo box (dropdown) there is nothing in the list (the list area drops down, but is totally blank). I tried this with adding a new record with the same result.

I've created hundreds of combo boxes and never had this happen. I've compared the settings with working combo boxes on the same form and can find no relevant differences.

I am totally stumped. I know this has to be something stupid I'm overlooking, but I've got a total brain freeze about this problem.

If anyone can give me a suggestion of something I might have overlooked or something else to try (I've already tried creating a totally new combo box and got the exact same result (and this was even using the combo box wizard)).

TIA to anyone who responds with any helpful suggestions.

George




Good day to all. I have a list box named lbxReports with several report names on it and i want to specifically call a set of row source on the chosen report name to the combo box named cboFilterby using Value list as the row source type and this will be triggered via after update event.

I tried to use this (but didn't work at the moment):

Private Sub lbxReports_AfterUpdate()

if me.lbxReports = "Payment Summary" then
me.cboFilterBy.Rowsource = _
"Contact Name";"Account Type";"Transaction Name"

End if

End Sub




Good Day. I'm using access 2007 and I'm trying to find a way to allow users to add two or more "New Data" to a combo box, and then opening the client form reflecting the new data entered from the combo box. So that the user can add more fields that are required before allowing them to save the form and after the form is saved and closed, i want the combo box to be automatically refreshed and the new data selected itself. By then all that is left to the user is to push tab to go to the next control.

What I've accomplished so far below my code example is adding only a single "NewData" from the combo box and then opening the client form to add some more. but what if i want to add (2)two or more first names and a last name like for example: "Marky Mark Whalberg"
Where Marky Mark is the (2)two first names and Whalberg is of course the last name? I want this automatically reflected on the client form upon opening it and then after the user entered the other required fields,saved and closed the form. I want the calling combo box to be automatically refreshed and the new name selected and if possible would also automatically go to the next control. Thank you in advance.


	Code:
	Dim strSQL As String
Dim strMSG As String
Dim lngClientID As Long
Dim ctl As Control
Set ctl = Screen.ActiveControl

    strMSG = "The name, """ & NewData & """, you entered is not listed. Do you want to add it?"
    
    If MsgBox(strMSG, vbYesNo, "NOT LISTED") = vbYes Then
        strSQL = "INSERT INTO tblClients (FirstName)" & "SELECT """ & NewData & """"
        CurrentDb.Execute strSQL
        Response = acDataErrAdded
        lngClientID = DMax("ClientID", "tblClients")
        DoCmd.OpenForm "frmClients", , , "ClientID = " & lngClientID
        DoCmd.GoToControl "LastName"
    
    Else
        ctl.Undo
        Response = acDataErrContinue
    
    End If





Hello,
I have a combo box with three fields: ID, Funds, Exceptions. These fields are linked to a query and the query has been created from a table called "DailyPrices". I would like to use the ID record from the Combo box to search for it in the table "DailyPrices" and provide me the information contained in another field of the table called "Exceptions" which is related to that particular ID. The "Exceptions" field from the table is a memo field. I would like to include this field in the form which includes the combo box is so users can update the table using the form. is this possible?

I tried to use a text box linked to the combo box including Dlookup in the data control source but it doens't work. I read that memo fields can't be indexed, is that the problem?

Jose




I have a minor problem that makes me frustrated!
I have managed to add All to a combo box when using fields from a table as rowsource. However, I want to use fields from a query instead and this causes an error:

Too few parameters. Expected 3.

Any help on this issue is welcomed!




Hi all, a newbie with novice access skills.

I'm very good with utilizing the access tools however I can only code in vb editor minimally. What I'm looking to do is have the option buttons control two combo boxes. One to search by user name and the other to search by serial #. I've created an option group with two buttons I set the default values in the combo boxes in accordance with the option buttons However, the option buttons aren't doing what they are supposed to do. I've been told I need to tweak the code for the buttons in order to make them control the combo boxes. Does anybody know what I need to do to make this happen? Ultimately the user will be able to click either of the buttons to perform a search through a single combo box.

Thanks to everyone out there for making this possible. Your expert knowledge will help me grow into an expert myself.

Thanks to all in advance!