Real Software Forums

The forum for Real Studio and other Real Software products.
[ REAL Software Website | Board Index ]
It is currently Sat Sep 21, 2019 8:35 am
xojo

All times are UTC - 5 hours




Post new topic Reply to topic  [ 8 posts ] 
Author Message
 Post subject: Populate ComboBox or PopupMenu with query from DB
PostPosted: Tue Dec 09, 2008 1:52 pm 
Offline

Joined: Fri Sep 14, 2007 5:00 pm
Posts: 408
I am trying to populate a ComboBox or even a PopupMenu with a query. But I seem to have run into the idiot's box again.

// This returns the meter information of the selected meter
// Variable declarations
Dim dbRec As New DatabaseRecord
Dim stSql As String
Dim recSet As RecordSet
Dim dBse As New REALSQLDatabase
Dim dbFile As FolderItem
Dim intI, intLast As Integer


dbFile = GetFolderItem("dbMaintDB.rsd")
dBse.DatabaseFile = dbFile

' Test to make sure I am connected to the database.
If dBse.Connect = True Then

stSql = "SELECT caan FROM tblBuildingData"

recSet = dBse.SQLSelect(stSql)

If recSet = Nil Then

Beep
MsgBox "Hmmm, connected but the RecordSet is empty?!"
Exit Sub

Else

' While Not recSet.EOF
intLast = CountFields (recSet)

For intI = 1 To intLast
ComboBox1.AddRow NthField(recSet,)
Next


' Wend

End If

End If

recSet.Close


I got the code idea from the Language Reference but it appears I am not getting it quite right.

I am getting an error here --> intLast = CountFields (recSet)
The error is "This method requires more parameters than were passed."
So I go back and see that there is a ,"," in the Language Reference code and I put them in like so:

I am getting an error here --> intLast = CountFields (recSet,",")
And get this error: Parameters are not compatible with this function

Thanks again!


Top
 Profile  
Reply with quote  
 Post subject: Re: Populate ComboBox or PopupMenu with query from DB
PostPosted: Tue Dec 09, 2008 1:58 pm 
Offline

Joined: Fri Jan 06, 2006 3:21 pm
Posts: 12388
Location: Portland, OR USA
You appear to have mixed a couple of examples. CountFields/NthField operate on a String, not a RecordSet. Try
recSet = dBse.SQLSelect(stSql)

if recSet = nil then
msgbox "There was an error in my sql: "+ dBse.ErrorMessage
else
while not recSet.EOF
ComboBox.AddRow recSet.Field("caan").StringValue
recSet.MoveNext
wend
end

Tim


Top
 Profile  
Reply with quote  
 Post subject: Re: Populate ComboBox or PopupMenu with query from DB
PostPosted: Tue Dec 09, 2008 2:22 pm 
Offline

Joined: Fri Sep 14, 2007 5:00 pm
Posts: 408
Works great! LOL, Thanks! So I am wondering, is it possible to have two columns in a ComboBox or PopupMenu?


Top
 Profile  
Reply with quote  
 Post subject: Re: Populate ComboBox or PopupMenu with query from DB
PostPosted: Thu Dec 18, 2008 11:43 am 
Offline

Joined: Fri Sep 14, 2007 5:00 pm
Posts: 408
Next question. Okay so I can load the popup menus and combo boxes with the data I want from the database. But how do I get it to display a value based on another field?

What I mean is, I have an edit field that contains a Alpha numeric value. Part of that value matches with an item in a popup menu. I will give a detail with some actual data to help with my question.

EditField = "E12301"
PopupMenu = 250 returns based on initial query to populate it. In the PopupMenu there is a value : 7123 that is the match for the EditField.

I know how to create a variable by using the Mid function to extract the 123 from the EditField text and appending a 7 to the front. What I am not able to do now is to get that value to display without having to open the PopupMenu and select it while keeping all the other values available.

So I am trying to get the value to be visible and in memory for use later in the application.

--------------- New issues Below ------------------------

One other thing I have done, is I found that while I cannot have two columns in a PopupMenu or ComboBox, I can take two return values then concatenate them so they both display. The problem is when I select the PopupMenu item I want, it will populate two EditFields for me, but I can't seem to figure out how to trim the extra bit out.

Here is some data to show what I mean:

I am taking the database fields energyType,billRate and concatenating them to create a single entry for the user to select from.

So the entry in the PopupMenu looks like this:

Diesel Fuel - D-BAU
Electric - E-ARB
Electric - E-BAU
Electric - E-TI BLDGS
Natural Gas - G-BAU
Natural Gas - G-LML
Natural Gas - G-TI BLDGS

This allows the user to select the appropriate Energy Type as well as the proper billing rate to be applied. But the field Energy Type is only supposed to have entries like below:

Diesel Fuel
Electric
Natural Gas

The way this works is when a user enters a meter id the prefix will define the Energy Type. So if someone enters E12301 then the Energy Type will be one of the following:

Electric - E-ARB
Electric - E-BAU
Electric - E-TI BLDGS

This allows the user to then select the appropriate billing rate which in turn will populate two more EditFields with the appropriate information. This is to reduce user input error as well as the amount of point click and typing required. There is an exception clause that if a meter prefix falls under one of a couple categories then ALL Energy Types and Rates are to be displayed since they are what we call non bound meter types. I hope this helps to explain what I am doing and why I am doing it.

So there are two questions on how do I pre-load, reload a PopupMenu without wiping other data fields out. Thank you!


Top
 Profile  
Reply with quote  
 Post subject: Re: Populate ComboBox or PopupMenu with query from DB
PostPosted: Thu Dec 18, 2008 1:01 pm 
Offline

Joined: Fri Jan 06, 2006 3:21 pm
Posts: 12388
Location: Portland, OR USA
Quote:
So there are two questions on how do I pre-load, reload a PopupMenu without wiping other data fields out.

You don't. You shouldn't use the PopupMenu to store data like that. Store the data in some other structure - arrays, dictionary, custom class, whatever - and populate the popup with just the data you want the user to select - Diesel Fuel, Electric, Natural Gas. When the user selects from the popup, then you go to your data, find the matching information and populate another field with the billing rate. Don't try to overload the popupmenu.

You can select an item in a popupmenu from code by setting the ListIndex property.

Tim


Top
 Profile  
Reply with quote  
 Post subject: Re: Populate ComboBox or PopupMenu with query from DB
PostPosted: Thu Dec 18, 2008 3:13 pm 
Offline

Joined: Fri Sep 14, 2007 5:00 pm
Posts: 408
How this works is pretty simple.

EditFields
txtMeterId
txtMeterName
txtMeterLocation
txtMeterAccess
txtManufacture
txtMeterSerNum
txtFloors
txtRooms
txtToilets
txtGrsSqFeet
txtBldgMngr
txtUnitOfMeasure
txtBillingRate
txtShopAssigned

PopupMenus
popMenEnergyType
popMenBuildingCode

How this works (currently in Microsoft Access and partially in my REALBasic application)

User enters a Meter ID into txtMeterId
txtMeterId loses focus the following happens

txtMeterName, txtMeterLocation, txtMeterAccess, txtManufacture, txtMeterSerNum are all populated from tblMeters based on the Meter ID.

popMenBuildingCode (this is called cboBldgCode in Access) displays the correct building code and is loaded with remaining building codes if the selected one is incorrect.

Based on the new value in popMenBuildingCode the following happens:

txtFloors, txtRooms, txtToilets, txtGrsSqFeet, txtBldgMngr is populated from tblBuildings based on the 4 digit building code.

The prefix of the Meter ID will then select the appropriate items in popMenEnergyType and display them in Access like so:

cboEnergyType.Column0 = Energy Type
cboEnergyType.Column1 = Billing Rate
cboEnergyType.Column2 = Unit of Measure

When the Combo Box is not actively open only column0 is displayed on the screen. When a user activates the Combo Box then all three columns are displayed. The user then selects the appropriate line item and then the following will happen:

txtUnitOfMeasure, txtBillingRate will be populated from the appropriate columns in the Combo Box and txtShopAssigned is populated from a pre-defined table in the code based on the prefix of the Meter ID.

The Listbox is the only element I have seen that has this potential BUT it's a static display in that it requires more real estate and displays all elements all the time. The PopupMenu and ComboBox display only one value but expand to allow for more selections, the problem is they do not seem to have the ability to display in an array format.

Does that clarify what I am trying to do? If there is a way to get this same ability in REALBasic then that is what I am after. If not then that means I will have to add more steps to the end user, which is exactly the opposite of what I want. Essentially there are 16 elements that are updated with a single entry. I have that currently in MS Access and am trying to replicate that in REALBasic.


Top
 Profile  
Reply with quote  
 Post subject: Re: Populate ComboBox or PopupMenu with query from DB
PostPosted: Thu Dec 18, 2008 5:59 pm 
Offline

Joined: Fri Jan 06, 2006 3:21 pm
Posts: 12388
Location: Portland, OR USA
You can do all of that in RB. The only catch is you have to simulate a multi-column popupmenu with an editfield and a listbox. You can initially place the listbox offscreen and bring into place when the field wants to "pop up", and take offscreen again when you're done. Or find a reasonable location for it on your window and leave it in place.

You probably want to approach each of the pieces you've outlined one at a time. Start with
Quote:
User enters a Meter ID into txtMeterId
txtMeterId loses focus the following happens

txtMeterName, txtMeterLocation, txtMeterAccess, txtManufacture, txtMeterSerNum are all populated from tblMeters based on the Meter ID.

and work your way from there.

Tim


Top
 Profile  
Reply with quote  
 Post subject: Re: Populate ComboBox or PopupMenu with query from DB
PostPosted: Thu Dec 18, 2008 9:18 pm 
Offline

Joined: Fri Sep 14, 2007 5:00 pm
Posts: 408
Thanks! I will hammer at that tonight.


Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 8 posts ] 

All times are UTC - 5 hours


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
Jump to:  
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group