Selecting two fields from one list box in Base

[stinsonpilot]

Here is what I am doing. I am a Ham radio operator and I need to create a simple database contact logging program. The fields I need are “Date/Time”, “Signal Report”, " the U.S. State of the person contacted" and the “points” (score) assigned to that specific U.S. State. The logic is that a smaller state is more difficult to work so it should have more “points” assigned to it.

This is where I am at so far. I can auto update the Data/Time, I have that covered. I also assigned a default signal report, got that covered. What I need is a way to use a List Box that displays the U.S. state along with its associated numerical point value. These two pieces of information can now populate the “State” and “Points” fields in the table logbook. I can click the list box and assign a state or a point value but not both.

Thanks! Inquiring minds want to know.

Lowell, KD8HD

[RobertG]

A list box shows an information in the form. By default it will save another information to the database. All the content will be filled by SQL like this: SELECT "Show", "Save" FROM "Table".
You could read “Save” from the current value of the list box:

oControl = oForm.getByName("NameOfListbox")
stSave = oControl.getCurrentValue()

You could read “Sow” by the position of the selected Item:

oControl = oForm.getByName("NameOfListbox")
stShow = oControl.ValueItemList( oControl.SelectedItems(0) )

Don’t know if this helps.

[stinsonpilot]

Thanks RobertG. I’ll digest this and give it a try. I’ll get back to you.

[skyandrews]

Hello again,

I stumbled upon a possible solution for you.
set strA=yourlistBox.getItemText() - This is the text that is showing in the list box. (string)
set varB=yourlistBox.selectedValue() - This is the value of the listbox’s bound column. (variant or integer)
You can then use the strA and varB variables for macros or to save in tables.

[stinsonpilot]

Thanks, I’m looking through your config. I’ll get back with you after I do.

[stinsonpilot]

Can you please explain the query statement you are using: SELECT (“contactName”+’ - From: '+“stateName”), “Contacts”.“ID” FROM “Contacts”,“States” WHERE “Contacts”.“stateID” = “States”.“ID”

I don’t understand the use of the “+” and the " : " I see in the results you get the contact name and the state but I don’t see how the sql statement gave us these results.

Thanks!

[Wanderer]

The plus sign + serves the same purpose as the operator || in the answer by @UnklDonald , it concatenates text. Some use a function concat() instead.
.
The : has no meaning as it is inside single quotes ' so it is only text. replace with whatever text you wish to have here - a simple ', ' should be sufficient to separate both parts

[skyandrews]

The + sign is the concat function I prefer to use. It is one alternative to using CONCAT(“contactName”, ’ From: ', “stateName”). Anything inside the single ticks, aka ’ From: ', is simply a string, with spacing, to include in the concatenation. Note: Concatenating with the + requires using sql (native) in the control data properties.
Learning how to write SELECT statements is probably the most important aspect of creating a proper database I have used. Punctuation is very important: ’ string ’ , ““Table””."" tblColumn “”, ’ " & strA & " ', " & varA & ", etc… Each has a specific purpose, use, and context. There are tons of good resources on writing SQL statements. I prefer w3 schools.

[Wanderer]

@skyandrews

Punctuation is very important:

And on this site using backticks ` around any quotation, to prevent changes from straight ' ' to curly variants ’ ’

[UnklDonald]

One approach would be to use a table “states”

CREATE TABLE "states" (
"ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0)
NOT NULL PRIMARY KEY,
"state_nm" VARCHAR(20),
"points" INTEGER
);

Once data is entered then use a listbox query that will display both the state and the points

SELECT "state_nm" || ' - ' || "points" || ' Pts', "ID" FROM "states"

Using this approach the table “logbook” only needs one Integer field “State” to store “ID” value for the selected “state_nm”

There is no need for a “Points” field in logbook, in fact including one would create a potential data integrity problem ( i.e “Points” in logbook differ from “points” in the “states” table)

[stinsonpilot]

Thanks. I am digesting what you sent me. I’ll get back with you.

[stinsonpilot]

Thank you very much for your help! You all have been very helpful!!!