change listbox rowsource using vba

Hello, I'm trying to change the row source of a listbox using VBA. My listbox is set to show column heads and multi select is set to none. I copied my sql( actually two of them, one for each of the info I want to show) from a query that returns the proper info but when I set the row source in vba and requery the listbox the listbox is empty. I tried hard coded the sql in the listbox's row source(with the proper syntax changes) and it works fine. here's my two sql"s


	Code:
	Private Sub chkOpenReferrals_Click()
Debug.Print
Dim strSQL As String

If Me.chkOpenReferrals = -1 Then
strSQL = "SELECT tblHistory.TicketNum, tblHistory.StartDate, tblHistory.TermDate, tblContractor.ContractorName, tblHistory.ReferralNumber, tblHistory.ReferralComplete" & _
"FROM tblContractor INNER JOIN tblHistory ON tblContractor.[ContrID-PK] = tblHistory.ContrID" & _
" WHERE (((tblHistory.TicketNum)=[forms]![frmMain].[cboticketnum]) AND ((tblHistory.ReferralComplete)=False));"




ElseIf Me.chkOpenReferrals = 0 Then

strSQL = "SELECT tblHistory.TicketNum, tblHistory.StartDate, tblHistory.TermDate, tblContractor.ContractorName, tblHistory.ReferralNumber, tblHistory.ReferralComplete" & _
"FROM tblContractor INNER JOIN tblHistory ON tblContractor.[ContrID-PK] = tblHistory.ContrID" & _
" WHERE (((tblHistory.TicketNum)=[forms]![frmMain].[cboticketnum]));"




End If

Me.lstReferrals.RowSource = strSQL
Me.lstReferrals.Requery


End Sub

Any ideas on why I can't get this to show in the listbox?
Thanks, Jim


Post your answer or comment

comments powered by Disqus
I have a form that will quit Access at a certain time so that A backup script can run and maintenance can be performed. The form uses the timer event and displays a countdown in 10 second increments (the timer increment). I want the user to be able to finish whatever work they are working on and then exit on their own or just let Access quit automatically. The problem is that every 10 seconds the form pops up over the other forms where they are working, making it very unusable. I set the main forms that they use to PopUp = Yes. I also set the warning form to PopUp = Yes so that it will actually show up rather than be hidden by the front most form. My plan was to have a button that they could click that would change the PopUp value to false using VBA so the form could be sent to the back. I get runtime error 2448 You can't assign a value to this object. Is this true? I thought you could change any value using VBA. I used the syntax Me.PopUp = False and Forms!frmAppShutDownWarn.PopUp = False.

Anyone know how to change the popup value or else a way to keep that form down?

Sorry for the long post, just trying to add all the details

Greg

Hi guys

I have a query "Query1" in queries tab and now I want to change the criteria in that query. In existing query1 the criteria is "ProjectRef=P4" and I want to change it to P5.

Can anyone please help me how can we change the criteria using vba?

Thanks

Hi there

Is it possible to change the ColumnCount of a combobox using VBA?
I want to have the Filter2 combobox listed below to use the column to it's right to give me the "Description" of the Item that is selected in the Filter2 and then this text will be output to my report as below...


	Code:
	 Reports![rptFilterDetail].txtReportTitle.Value = _
        "System: " & Me.Filter1.Value _
        & vbCrLf & "Subsystem: " & Me.Filter2.Value _
        & vbCrLf & "Description: " & Me.Filter2.Value

Many thanks
Batwings

Guys,

I have successfully develop login system using VBA, only one thing left, that is "Change Password".
Now I want to develop a form where user will be able to change his own password after loggin in.

Kindly help me out in this regard.

Hi

I'm trying to create a report that would display data from different tables with similar structure.

I have some 30 different tables which outline 5 different products for different years. It is impossible to put all of them in one big table since each of them are used by different department and hence have different field structures.

All of them however contain fields which I will try to display in the report. The fields are:

ProductName, Supply, Return, NetSale

Since it would be inefficient to create 30 different reports that would reference to each table, I am thinking to design one single report to display the desired fields and get it to point to different tables using VBA.

From posted threads, I know it is possible to modify the RecordsSource property using VBA on open report event. However, until now, I still am not able to get it working.

Can someone help?


Cheers

Hi

I have a list box which displays sales figures - the data has been transformed into a more user-friendly layout using sql in a complicated vb process.

On the same form i wish to create a button which the user can press to export the listbox data/layout onto a spreadsheet.

Im hoping for the button to grab the rowsource of the listbox and use this sql to specify the query in the export statement - is this possible? (This will prevent me having to change the already complicated vb process)


ie. i tried this but cannot get it working!

getRowSource = me.salesbox.rowsource

DoCmd.TransferSpreadsheet asExport, asSpreadsheetTypeExcel9, getRowSource, "C:myfolderexports

Many Thanks

Hi there,

My apologies to those whoe have already read this post - I originally tacked it onto an existing thread, but just thought I would repost to try to get a solution.

My situation is that form A and form B can be used to open form C. However, depending on which form is used to reach form C, I need to change the RowSource of a ListBox on form C to one of two queries.

I am having all sorts of trouble. Once the 'open' command is fired from either form A or B, the listbox is immediately looking for parameters for the queries before any code can be used to set the rowsource in either the Open, Load or Activate events.

This is the code from forms A and B which open form C:

	Code:
	'Open form
DoCmd.OpenForm "frmOutputs_9_Track_Invoices", , , , , , 2


	Code:
	'Open form
DoCmd.OpenForm "frmOutputs_9_Track_Invoices", , , , , , 1

This is the code for when form C opens:

	Code:
	Private Sub Form_Open(Cancel As Integer)

Select Case Me.OpenArgs
    Case 1 'frmOutputs_2_Specific_Search_Results' was used
        Me.lstResults.RowSource = "qryOutputs_9_Track_Invoices1"
    Case 2 'frmInputs_14_Track_Invoice' was used
        Me.lstResults.RowSource = "qryOutputs_9_Track_Invoices2"
End Select

End Sub

I have even tried resetting the rowsource to an empty string on closing form C, but this won't save!!! The rowsource is still set to one of the queries when I open form C again.


	Code:
	Private Sub Form_Close()

Me.lstResults.RowSource = ""
DoCmd.Save acForm, Me.Name

End Sub

Any suggestions are very welcome!

Hello all ye experts.

Can you please tell me whether one can use VBA to change a label on a report ?
I intend to use text and a variable to insert in the label.

Many thanks.

LouisB

Hello,

Please can someone tell me if I can change a table field properties using VBA?
I have a table which imports a field as Date, but I want to change it to Text.

Thanks for any advice.

Hi,

I'm doing a shop database, and I'm using the default value to store VAT values.

Is there a way to change the default value on a number of tables using VBA code or macros?

Cheers,

Tom.

Is there a way to rename the fields in queries using vba. I am using the code below but get the error "Can't assign to read only property." at the point that I have highlighted.

The reason that I need to do this is because I am renaming all the fields in the tables to eliminate white spaces. I then need to run code to try and catch all these changes in as many queries, forms, reports and calculations as possible. The query part is beating me up right now.....



Function ChangeQueryFieldNames()
Dim db As DAO.Database, tdf As DAO.TableDef, fld As DAO.Field
Set db = CurrentDb
For Each qry In db.QueryDefs
For Each fld In qry.Fields
fld.SourceField = trim(fld.Sourcefield) '

Hi guys,
I have 2 combo boxes.

ComboBox1 is the field list from a query:MyQuery (for example)
I can select any field in that query

ComboBox2 is the content results of the Field selected by ComboBox1

If I've selected the field INVOICE in ComboBox1
then I see the list of INVOICES in that field in ComboBox2 and can select one of them from ComboBox2 to use when executing the next procedure,

which is to FIND and display all records (Line items) that match THAT INVOICE.

OK, I've been programatically able to change the rowsource for ComboBox2

BUT, there are some records for which the INVOICE data wasn't entered so I get 2 visually blank rows at the top of my list in ComboBox2, which I want to get rid of.
ONE of those rows represents NULL values
THE OTHER row represents EMPTY/BLANK values equal to ""OK, I've been programatically able to account for those and exclude them by having VBA create the Rowsource for ComboBox2 as follows:
SELECT DISTINCT MyQuery.INVOICE FROM MyQuery WHERE (((MyQuery.INVOICE) Is Not Null) AND (Not(MyQuery.INVOICE) ="")) ORDER BY MyQuery.INVOICE;
SELECT DISTINCT - keeps multiple instances of the same INVOICE number out of the list and just presents 1 INVOICE number for a group of records that share that same number

WHERE (((MyQuery.INVOICE) Is Not Null) AND (Not(MyQuery.INVOICE) ="")) - effectively eliminates any NULL values and any "" values from the list in the query.

The whole SQL string is set to a variable strSQL , strSQL represents the entire SQL string

Just prior to setting the ComboBox2.rowsource I've been using a trouble shooting technique that we are familiar with to verify that the strSQL is exactly what I want : Debug.Print strSQL

The strSQL is Exactly correct and can be verified by copying the SQL from the Debug.Print and pasting it into a blank query in SQL (design) view then running the query it produces exactly the results I want for the field list.

Then I ATTEMPT to plug it into the rowsource of Combobox2 like this:
ComboBox2.rowsource = strSQLNOW FOR THE ISSUE:

BUT, the null and blank fields still show at the top in the ComboBox2 list. When I examine the rowsource property of the ComboBox2 it has stripped the WHERE clause in the strSQL statement.

Any idea why I can't pass the pure string to the comboBox2.rowsource property as is?

Help!
Goh

I've never been good at sql syntax. I'm trying to change the sorting order of a listbox using an option group. I can use sql to change the rowsource some of the time but not all the time. So I conclude that I don't understand something somewhere.


	Code:
	    Dim strAD As String     'Acending Decending
    Dim eom As String
    eom = DateSerial(Year(Date), Month(Date) + 1, 0)
    
    If Me.frAD  1 Then strAD = "DESC"
    
    Select Case Me.frSort
        Case 1 'PName
            Me.lstPtList.RowSource = "SELECT * FROM qryRecallPtList ORDER BY QryRecallPtList.PName " & strAD
        Case 2  'Last Visit
            Me.lstPtList.RowSource = "SELECT * FROM qryRecallPtList ORDER BY QryRecallPtList.LastVisit " & strAD
        Case 3  'Recall Date
            Me.lstPtList.RowSource = "SELECT * FROM qryRecallPtList ORDER BY QryRecallPtList.Recall " & strAD
        Case 4  'Up to end of this month
            Me.lstPtList.RowSource = "SELECT * FROM qryRecallPtList ORDER BY QryRecallPtList.Recall " & strAD & _
                                    "WHERE QryRecallPtList.Recall < #" & eom & "#"
        Case 5  'up to end of next month
    End Select

Everything works until is use Case 4. Then my listbox goes blank. I can't seem to figure out the 'WHERE' syntax and spacing. Can someone tell me what is wrong and why its' wrong? Thanks you.

Hey all,

I have a number of listboxes in a form who gather data about the main field in that form, so they are bound to queries etc.

The thing is, I have a routine that allows users to create new records, and in that instance the following code runs:

'[Forms]!customers.lstOpenCalls.RowSource = ""
'[Forms]!customers.lstClosedCalls.RowSource = ""
'[Forms]!customers.lstAssets.RowSource = ""
'[Forms]!customers.lstLogins.RowSource = ""
'[Forms]!customers.lstCarePacks.RowSource = ""
'[Forms]!customers.lstConfigs.RowSource = ""
'[Forms]!customers.Customer = NewCustomer
'[Forms]!customers.[Address 1].SetFocus

(without the 's of course). This is so that those listboxes don't have old data in for the new record. The problem of doing things this way is that the rowsource is then permanently changed and my queries are lost, as I found out to my annoyance! Is there a better way of doing this?

I have two tables:

tblEmployees and tblSalesFeb2007

tables are joined (fields EE Name and EEID) within a select query
qrySummSales

Is it possible using VBA to change the name of the second
table to tblSalesMar2007 and then run the query?

Best Regards.

I need to automatically change the datatype of one of the columns in an Access table from Binary to Number using VBA. I tried using the code below to do this but get the runtime error 3219: Invalid Operation.

Is there any way I can do this?


	Code:
	Public Sub ChangeColumnType()


	Code:
	 
        Dim db As DAO.Database 
        Dim tbl As DAO.TableDef 

        Set db = CurrentDb 
        Set tbl = db.TableDefs("table1") 
        tbl.Fields("column1").Type = dbBigInt 

        Set tbl = Nothing 
        Set db = Nothing 
End Sub



Thank you in advance for any help.


Hi everyone,

Can i change the color format of the datasheet part of a split form using vba?

Can I make columns disappear in the datsheet part while the respective field on the form remains in place ?

Any help from the forum please ?

LouisB

A few questions regarding graphing in Access...

How do I change the recordsource for a chart so that the y-axis becomes the category axis and numerous fields can
be plotted against this y-axis?

In Excel graphing what I am trying to graph is simple (XY Scatterplot Chart), for each series the x-axis and y-axis can easily be chosen...But in Access I can not figure out
how it works. Is there a way in Access to manually program several series to graph..where you can separately
choose the x-axis and y-axis of several series and then plot them all on the same graph?

I want to graph various data versus depth in the ground...so I want depth on the y-axis and several fields plotted against depth (on a line graph)

I could plot one set of data versus depth using SQL: SELECT Data1, Depth ...but I need to be able to plot many sets of data versus the depth.

I haven't figured out how to rotate the chart either...but have been able to plot a bar graph with several sets of data plotted on the x-axis versus depth on the y-axis...but haven't been able to switch this to a line graph while still keeping the y-axis as the category axis.

I've also attempted to insert an unbound object as an excel chart. This way I can choose the data like I would in excel (in which case it is easy to graph what I am attempting to do in access). But I haven't had any success in selecting data from a query for the x and y axis rowsources.

I was hoping there was a way to graph in Access using VBA coding. I haven't had much success in finding information on this topic. Using a macro recorder in Excel I have viewed the code that excel used to create the desired chart. ..but when programming with VBA in Access, things such as .charttype are not recognized.

Anyone have any ideas? Thanks.

I'm not sure if this is the correct place to post this, but I have a database that I created in Access 2003, that was working fine until a few of the users got Access 2007. I did not convert the database since the majority of users are still using 2003.

Anyway there is one form where users select multiple combo boxes (each populated based on the previous combobox selection). Additionally, a list box populates based on the first combo box selection.

For some reason the list box stopped populating for Access 2003 users. I'm using pretty generic code to update the listbox:

strSQL = "queryinfohere"
listbox.RowSource = strSQL

I haven't changed anything in the code.

Any ideas why this would suddenly stop working only for Access 2003 users?

Thanks!

Hi there,

I asked this question in the report forum, but wasn't able to solve my issue.

I know that it must be possible to change the series name in a chart using VBA, but am not at all experienced with writing it myself.

Is anyone able to help with some code to enable me to do this?

Any help much apreciated,

Kirsti

I know this is probably simple but how do you change the form you are looking at to edit mode using vba?

Hi All ,
I have created a MS Access barchart by running a query. X axix has user name and y axis has counts. I would like to change every bar's color based on the x axis user name value .Is there a way to do that using vba code in MSAccess barcharts. Kindly advise.

Hi,

I am currently working a a Junior IT and am new to Access and databases in general. I have been tasked at finding a way to integrate an activex object into our database.

What I am trying to do is have it so that when we load a report the object is autmatically populated with the relevant data from a table. Unforunetly this particular control does not have a "Control Source" property, otherwise I would just modify that. I have successfully made it so that I can change the property of the object in question using vba. What I would like to know is if there is any way I can have vba insert table data, much like I would do using "control source" (if it were available).

I want to change the page setup on a report using VBA code. Is it possible?

What I want to do is to change the paper size, margin, printer, not using the dialog window, but using code.

Any idea?
Thank's for help.

Rilles Eko Prianto

[This message has been edited by Rilles (edited 05-28-2001).]


Not finding an answer? Try a Google search.