[Solved] Reading/Searching values of a table field

[EricZamok]

I am trying to teach myself LibreOffice Base and Basic – the learning curve is steep. I have constructed a toy database which I will further simplify to set out my problem:

I have tables:
“Users”, which contains two fields “Nickname” and “RealName”;
“Comments” which contains two fields “Nickname” and “Comment”
(Each also contains an automatically generated primary key which may not be strictly necessary as “Nickname” needs to be unique – but we are talking toy!)

There are forms,
“NewUser” which allows a new user to input her nickname of choice (Nickname) and her real name (“RealName”) - a toy registration process;
“NewComment” with two controls “Nickname” and “Comment” which invites the user to leave a comment.

When a user tries to add a comment, the nickname he enters into the NewComment form may not be in the Users table and thus he is not registered and so not allowed to leave a comment.

How do I check that the entered nickname appears in the Users table? The intention is that if it does NOT the user is invited to register and provides his real name as “surety” (and, at the next level of sophistication, then invited to leave his comment).

It seems to me that there should be (but is there?) a simple ('cos I'm simple minded) way of searching a designated field in a designated table for a string (remembering that each nickname will be unique). But I haven't been able to a Google search to find it or find it here.

I am aware of the extension Access2Base (and found it useful) but I haven't been able to find anything within it that helps me – which may be an expression of my ignorance.

Help would be appreciated. And if the explanation is in words of one syllable I may be able to understand it!

Many thanks

Solution:
A solution can be cobbled together by referring to the Tutorial section of Access2Base.com:
From the table Users construct a sub set consisting of those records which contain the target nickname (Easy How to's #5), count the number of records in it (Easy How to's #7). Because the Nicknames are unique the possible results of the counting are 1 and 0 (the nickname is/isn't there). Have a function return this value as a Boolean (by happy coincidence integer 1 is interpreted as Boolean True, integer 0 is interpreted as false - but you probably knew that).
There are probably more elegant solutions but this one will do me for now. (I still have to work out how to convert that True/False into the appropriate action but that can't be difficult ......can it?)
In my ignorance I traipsed up a good number of blind alleys but I got there in the end - but, at least, I tripped over interesting facts whilst traipsing!
Many thanks to JPL for Access2Base.

[DACM]

How do I check that the entered nickname appears in the Users table?

I would recommend a List Box on your AddComment form. The List Box can be populated by the USERS table (drop-down user selection by NICKNAME as necessary), while saving the associated USER 'primary key' to the COMMENTS table. There's no macro coding involved with this approach.

[FJCC]

The database engine will take care of this for you if you create a Foreign Key relationship between Users.Nickname and Comments.Nickname. This will be a one-to-many relationship because one user may leave many comments. Look in the menu Tools -> Relationships for a GUI with which to set up the relationship. I haven't done that in a long time, so the details are hazy.

You could use a form control to limit the Nickname choices available on the NewComment form to those that are present in the Users table.

[EricZamok]

FJCC and DACM

Thank you for taking the time to respond.

DACM
In fact, my first thought was that a ListBox would be the way to go but, if I understand it correctly, a ListBox construct limits the choice of Nickname in the NewComments form to just those in the Users table; I want to allow the user to input a nickname that doesn't appear there. A ComboBox (again, by my understanding) seems to allow for the addition of a new nickname which can be used in future instances of NewComment but does not enforce the need to add the real name (which is a necessity). In Access there is an event (possibly, it's a long time since I used Access) called NotInList that gives a handle that allows for the updating of a ListBox contents (through Basic code); such a thing does not seem to be available in Base. I had though to use a simple InputBox to capture the nickname from the NewComment form and see whether it was included in the Users table (hence the "searching" in the post title) but I was stymied there too.

FJCC
As I said, I'm new to Base so I don't fully understand your suggestion (it contains words with more than one syllable!) but on my first reading it seems to fall foul of the same points as I've made above. However, I'll do some more reading to see if I'm missing something.
I've done some more reading and (I think) that, in essence, you're suggesting a different way of setting up the same sort of relationship as DACM was suggesting. (If I'm wrong, please correct me.) As such it does fall foul of the points I made above.

Perhaps I haven't made my intention clear: If an unregistered user tries to enter a comment they are "thrown" into the NewUser form, having completed that they can then make the comment they were previously prevented from making. (I admit the process as set out lacks elegance but that may follow later.)

Again, my thanks