Base - form for data entry multiple tables

[Korpu]

Hello.

I am desperate and i can’t find the answer by my own. I created my 1st database, created relations, etc. But I am struggling with creating form for better data entry. Here is an example of my issue.
New Database example.odb (5.1 KB)
This is just example for better understanding what I need, not my database.

I want to input data into table Client. Main table Client contains ID_Street which is foreing key from table Street. I don’t want to manually search through table Street and search for corresponding ID_Street. In form I want to choose from listbox. And in this listbox I want to see Street_name from table Street but I want form to save corresponding ID_Street from table Street to my main table Client. Hope this make sense and I would be really happy for help. I spent hours watching tutorials but every tutorial exlains creating entry form for just one table database.
Thank you so much

[Ratslinger]

Hello,

The relation you are attempting is reversed of what it should be. The Street table needs to link back to the Client. With this relation there is no need for any list box. By setting the Client as a main internal form and Street as a sub form the link can be automated.

Sample → New Database example.odb (12.6 KB)

Edit:

Solution 2 using list box → New Database example.odb (13.2 KB)

The list box settings are in the controls properties on the Data tab. This one is based upon SQL. Two fields are selected - name first and ID second. The first item selected is the displayed item (data can be concatenated). The stored data is based upon the setting of the bound field. It is a zero based selection so zero will use the first field and one the second. Here it is a 1 so the ID will be stored.

[Korpu]

Hello.

Thank you so much for you answer. Your 2nd solution is exactly what I need. Because in my actual database, there will be need for much more listbox choices (street, city, group, etc).

I am trying to learn how you did it, but I must be doing something wrong (or I am just too big newbie for this ;))

I open your form in edit mode, select form field, right click, control properties - no data tab there

If I choose form properties instead of control properties, now I can see data tab, but I dont think this is what you meant.

I am trying to figure out “The list box settings are in the controls properties on the Data tab. This one is based upon SQL.”

[Korpu]

Oh, nvm. I think I got it.

Can you check it, if it’s correct? But it’s working as intended, so I guess it is ;).

I had to cancel creation wizard while creating listbox and set everything manualy.

Thank you so much. I am happy again, I can finish my database.

[Ratslinger]

@Korpu

Can you check it,

Looks OK. With the table control, right mouse the column wanted and select Column.