How can I populate a more than one field on my form when I lookup a value from a table. (1 Viewer)

[bmaccess]

Hi there.
I am busy with my book system. I want to lookup the ISBN in a table and I need to populate the title with the book name as well.
Thanks for any help.
Hi all. I have read all your suggestions and I appreciate all. I understand that lookups is not a good way to fill in fields but in my current Booksystem I need to have to fill in the field Book Title from a lookup table. The lookup table in my case is the BookNumber table.
So in the image below when I am going to select 11 for the StudentBookIssue_ID , the name of the book Platinum Mathematics must be filled in the field BookTitle. I have attached my database for anybody who has the time and who would like to try and solve my problem. Thanks for all your help. I would really appreciate it if somebody can come up with a solution please.

View attachment 110320

 

[CJ_London]

not sure if your screenshot is of your table or a datasheet form. If the former, lookup fields in tables are a bad idea - see this link

The Access Web - The Evils of Lookup Fields in Tables

access.mvps.org

that said, one way is to modify the rowsource from say

SELECT PK, BookName FROM myTable

to

SELECT PK, PK & " - " & BookName FROM myTable

But as indicated in the link, what you see is then inconsistent with the field name and datatype

Better to have another control with a controlsource of

=StudentBookIssue_ID.column(1)

assuming your StudentBookIssue_ID rowsource is like the first example above

 

[GPGeorge]

Lookup fields are part of an interface in an application, NOT part of the data storage capabilities of a relational database application. They belong in interface objects, i.e. Forms, not in tables. Your problem here is that the confusion of those two has resulted in something that seems to offer a cool feature, but which actually adds confusion and complexity instead. Get rid of he lookup field in the table. Use the proper Foreign Key field. Your life will get smoother and your Access database more usable immediately.

 

[The_Doc_Man]

To do this from a combo box on a form is relatively easy. To do it from a lookup field in a table is hard. The reason is that from a form you have event code that can do all sorts of useful things behind the scenes. From a lookup field in a table you have no such events and therefore have no control over what you do.

It appears that you have a lookup field in a table, but it is possible that we are wrong. If that is actually a form's combo box, please clarify for us which environment you have. And the reason we need to know is that the answer is different for the two cases.

 

[bmaccess]

To do this from a combo box on a form is relatively easy. To do it from a lookup field in a table is hard. The reason is that from a form you have event code that can do all sorts of useful things behind the scenes. From a lookup field in a table you have no such events and therefore have no control over what you do.

It appears that you have a lookup field in a table, but it is possible that we are wrong. If that is actually a form's combo box, please clarify for us which environment you have. And the reason we need to know is that the answer is different for the two cases.

Hi there thanks for all the suggestions. The lookup is from a table called BookNumber. I just wonder if this was possible. If it is more difficult then combobox then I then I will switch to foreign keys to fill in field. I you do have a solution I will try that as well. Thanks

 

[bmaccess]

Lookup fields are part of an interface in an application, NOT part of the data storage capabilities of a relational database application. They belong in interface objects, i.e. Forms, not in tables. Your problem here is that the confusion of those two has resulted in something that seems to offer a cool feature, but which actually adds confusion and complexity instead. Get rid of he lookup field in the table. Use the proper Foreign Key field. Your life will get smoother and your Access database more usable immediately.

Hi Mr George. Yes you 100% correct. I am trying the foreign key option and just have to point my control source where necessary. Thanks

 

[bmaccess]

not sure if your screenshot is of your table or a datasheet form. If the former, lookup fields in tables are a bad idea - see this link

The Access Web - The Evils of Lookup Fields in Tables

access.mvps.org

that said, one way is to modify the rowsource from say

SELECT PK, BookName FROM myTable

to

SELECT PK, PK & " - " & BookName FROM myTable

But as indicated in the link, what you see is then inconsistent with the field name and datatype

Better to have another control with a controlsource of

=StudentBookIssue_ID.column(1)

assuming your StudentBookIssue_ID rowsource is like the first example above

Hi there. The lookup is from a table. So I decided to modify and use foreign keys and the correct control source to fill in fields. Thanks for advice

 

[Pat Hartman]

Once you get rid of the table level lookups, you can change the RowSource query of the combo to concatenate the two columns you want to be able to see when it is closed.

Select PK, fld1 & "-" & fld2 As Concat From YourTable.
Order by fld1 & "-" & fld2

 

[bmaccess]

not sure if your screenshot is of your table or a datasheet form. If the former, lookup fields in tables are a bad idea - see this link

The Access Web - The Evils of Lookup Fields in Tables

access.mvps.org

that said, one way is to modify the rowsource from say

SELECT PK, BookName FROM myTable

to

SELECT PK, PK & " - " & BookName FROM myTable

But as indicated in the link, what you see is then inconsistent with the field name and datatype

Better to have another control with a controlsource of

=StudentBookIssue_ID.column(1)

assuming your StudentBookIssue_ID rowsource is like the first example above

Hi there.
I am busy with my book system. I want to lookup the ISBN in a table and I need to populate the title with the book name as well.
Thanks for any help.

View attachment 110320

Hi there.
I am busy with my book system. I want to lookup the ISBN in a table and I need to populate the title with the book name as well.
Thanks for any help.

View attachment 110320

not sure if your screenshot is of your table or a datasheet form. If the former, lookup fields in tables are a bad idea - see this link

The Access Web - The Evils of Lookup Fields in Tables

access.mvps.org

that said, one way is to modify the rowsource from say

SELECT PK, BookName FROM myTable

to

SELECT PK, PK & " - " & BookName FROM myTable

But as indicated in the link, what you see is then inconsistent with the field name and datatype

Better to have another control with a controlsource of

=StudentBookIssue_ID.column(1)

assuming your StudentBookIssue_ID rowsource is like the first example above

not sure if your screenshot is of your table or a datasheet form. If the former, lookup fields in tables are a bad idea - see this link

The Access Web - The Evils of Lookup Fields in Tables

access.mvps.org

that said, one way is to modify the rowsource from say

SELECT PK, BookName FROM myTable

to

SELECT PK, PK & " - " & BookName FROM myTable

But as indicated in the link, what you see is then inconsistent with the field name and datatype

Better to have another control with a controlsource of

=StudentBookIssue_ID.column(1)

assuming your StudentBookIssue_ID rowsource is like the first example abov

 

[bmaccess]

Hi there. The lookup is from a table. So I decided to modify and use foreign keys and the correct control source to fill in fields. Thanks for advice

Hi there. Is it possible for you to try to assist me please . I need help fixing my rowsource problem. This is still my original problem. As you can see in the image and if you open the database. When I lookup 11 for StudentBookIssue_ID which will be inserted in the StudentBookIssue_ID field then the name of the book must be inserted in the BookTitle field form the lookup. My lookup table is BookNumber. I would appreciate any help thanks.

 

[CJ_London]

Use the columnwidth property to set the width of the first column to 0

 

[bmaccess]

Use the columnwidth property to set the width of the first column to 0

What I need is that the BookTitle field which is empty must be populated with the name. So 11 will go in first field and the book name must go in BookTitle field

 

[Gasman]

Refer to the relevant column of your combo to set your title.
Columns start at 0.

 

[Gasman]

Surely the studentbookissueid is going to be an autonumber, so the title control needs to be the combo?

 

[bmaccess]

Refer to the relevant column of your combo to set your title.
Columns start at 0.

Hi there. First of all this is not a combobox. I am looking up a value from a table. If I get the booknumber then the name must be added to the
BookTitle field. So if if choose StudentBookIssue_ID no 11 then the corresponding book name must be added to the field BookTitle which is the next field to it. I am attaching a copy of the database. I will appreciate it if you have time to have a look at my problem Thanks.
(Take Note: That the StudentBookIssue_ID will be a barcode number I will be scanning in)

 

[bmaccess]

Surely the studentbookissueid is going to be an autonumber, so the title control needs to be the combo?

Hello. No the StudentBookIssue_ID is going to be a number i will be scanning in using a barcode scanner. I need to populate the corresponding BookTitle wit the book name.I hope you understand what I am trying to do. You can download and open my database ans see how it will work.Thanks

 

[XPS35]

In the query that is the recordsource of the subform you get the title from the table LearnerBookIssue. First of all that field should not be in that table. Remove it and get the title from the booknumber table.

 

[Gasman]

Hi there. First of all this is not a combobox. I am looking up a value from a table. If I get the booknumber then the name must be added to the
BookTitle field. So if if choose StudentBookIssue_ID no 11 then the corresponding book name must be added to the field BookTitle which is the next field to it. I am attaching a copy of the database. I will appreciate it if you have time to have a look at my problem Thanks.
(Take Note: That the StudentBookIssue_ID will be a barcode number I will be scanning in)

View attachment 110373

I am on my phone. You are showing a combobox. That has columns, you show Column(1) as having the title?
Set your title control to that column in the afterupdate event of that combo.

 

[bmaccess]

Surely the studentbookissueid is going to be an autonumber, so the title control needs to be the combo?

Surely the studentbookissueid is going to be an autonumber, so the title control needs to be the combo?

Hi there. I put a wrong query there.Here is the new db.Thanks

In the query that is the recordsource of the subform you get the title from the table LearnerBookIssue. First of all that field should not be in that table. Remove it and get the title from the booknumber table.

Hi there. I place the wrong database. I did get title from BookNumber. I will put correct database.Thanks

 

[bmaccess]

In the query that is the recordsource of the subform you get the title from the table LearnerBookIssue. First of all that field should not be in that table. Remove it and get the title from the booknumber table.

Hi. I posted the wrong database query. Here is the correct one..Thanks for reminder BookNumber is correct table. Any suggestions how to fill both fields if i only search a number?