Subform or query help

[LadyoftheForest]

Okay, here goes my first help request. I’m getting so frustrated. I work 6 days a week so only have Sundays to work on this, and it feels like I search and search for answers and come close, but it doesn’t work, and then it’s 2AM in the morning and I just have to quit. And then forget where I left off the next time I try to work on it again. I know I am close, but I just can’t get the last piece. I've added the db below, stripped of all images so it would upload.

I’m basically a newbie again and really need simple step by step instructions. I’ve searched videos, walkthroughs and searched google til my eyes bleed and just cannot find what I need.

I know this should be a simple thing, I think, but for the life of me I can NOT figure out how to do this. And I don’t know if I need to build a query first before the subform.

I used the database an 11 year old thread on here as my basis and adjusted it to the way I liked.

I tried to add my database but keep getting the oops it's spam when posting soo..here's the long winded question

I have added a book table, and an ebook table with the different things I want to keep track of – title, author, ISBN, current price etc.

So now I want to add to the Flower Details form, a place (I think it should be subform) to be able to pull from the Book titles table, similar to an order list and instead of a quantity, it lets me add a page number, several numbers or a range of numbers for that plant found in the book, probably as short text. It only needs to show the Title, ShelfLocation and then the short text page number column.

I then would like to have a second “order” subform that does the same for the EbooksList. This one would only need to pull the Title and Hyperlink to the PDF file from the Ebooktable, I don’t need page numbers because they are searchable.

I’ve already created the joining tables in a many to many relationships for each table - Flower to Booklist and Flower to Ebooklist. But now I am just stuck trying to figure out my next step for creating a subform that allows me to pull from a dropdown list of titles, so that it pulls the remaining information automatically after I pick the title and then lets me add the page numbers where that plant is found for each plant.

If it matters, I don’t need to worry about orphans on any of the tables, because each list will have orphans - books that don’t relate to any of the flowers and flowers that don’t relate to any books. And I don’t want it to delete records from of the tables if I need to delete from the subform.

 

[LadyoftheForest]

Here's my database, evidently it wanted it's own post and the spam was because the forum didn't like the link to the 11 year old post from this forum.

 

[arnelgp]

which version do you have?
if it is older than 2010 then
if we modify, you won't be able to open it.

 

[LadyoftheForest]

which version do you have?
if it is older than 2010 then
if we modify, you won't be able to open it.

Thank you for your reply, Access 2016

 

[arnelgp]

there is "new" tab on flower form.
i don't know if this is what you want.

 

[LadyoftheForest]

there is "new" tab on flower form.
i don't know if this is what you want.

Oh my!!! Thank you so much! Yes, this is almost perfect!! is there a way to also pull the shelf location from the BookList form and the hyperlink from the ebook form?

 

[arnelgp]

here is the change.
you need to "widen" the Tab control.

 

[LadyoftheForest]

here is the change.
you need to "widen" the Tab control.

Ohhh thank you so much!! This is absolutely perfect! I literally have been trying to do this for weeks now!! I truly can't thank you enough!

 

[LadyoftheForest]

@arnelgp could you tell me the steps you did to create this. I am need of doing this again, but I really want to learn how to do this myself, because I hate continuing to ask for help.
This time it's for HerbalActions with an ID, ActionName(short text) and Actiondescription (long text) fields for each FlowerID.
I created a table, and I created the intermediate table a many to many relationships using the ID of both tables.
I would like for the just the ActionName and description to show in the embedded table. I don't need the FlowerID or the ActionID to show.
I know how to create the temporary table and paste it where I want if that is needed.
But I can't get it to pull the entered information from the description field, in the embedded table. ActionName field shows as a drop down list like I want, but that description stays blank.
Thank you for any guidance you can give.

 

[arnelgp]

firstly you need to create a Query using your 2 new table.
replace [workTable] with the name of your "2nd table".

select [workTable].FlowerID, [workTable].ActionID, [HerbalActions].ActionDescription
from [workTable] Left Join [HerbalActions]
on [worktable].ActionID = [HerbalActions].ID

create a datasheet form from the query.
on design view of the form, hide [FlowerID] field using
the Open event of the form:

Private Sub Form_Open(Cancel As Integer)
Me![FlowerID].ColumnHidden = True
End Sub

rigth-click Textbox [ActionID] and Change To->Combo Box.
on the New combo's Property->Data->Rowsource, click on the Ellipses (...)
drag the HerbalActions table and select:

select HerbalActions.ID, HerbalActions.ActionName from HerbalActions;

goto Format Page of Property Sheet.
on Column Count, 2.
Column Widths: 0";1"
List Width: 1"

save the form.

Bring frmFlowerDetails in design view and add another Tab page.
drag the new datasheet form (as subform) to the new tab.
click on the the outside border of the subform (when you click
make sure you see a Yellow box surrunding the subform).
go to Property sheet->Data and set the Link Master Fields/ Link Child Fields.

Link Master Fields: ID
Link Child Fields: FlowerID

save frmFlowerDetails form and test.

 

[LadyoftheForest]

Great! Thank you! I will try and see how I make out.

 

[Pat Hartman]

You seem to be happy with your app so far but books and eBooks should be in a single table rather than separate tables. With the exception of the hyperlink, they have the same attribute. Take a look at Amazon or any other site that sells books to confirm.

 

[LadyoftheForest]

Hi Pat, thanks for the insight, but I do want to keep them in separate tables. The PDF ebooks don’t need a page number since they are searchable once opened. They also don’t have a shelf location like my physical books do.
Yes, I am VERY familiar with Amazon and libraries in general as I am the library director here in our town. My ultimate goal was not to catalog my books per se, It was to create a master index of my physical books by plant thus saving the time of having to pull every book off the shelf and searching the the index for a single plant. All of the other information in the database including the ebooks and the catalog are just an added bonus at this point.

 

[Pat Hartman]

You can do whatever you want. I only offer "best practice" advice. Take it or leave it. Taking it doesn't cost anything at the start of a project and may ultimately end up saving you work down the road when you want to and a new feature. You came here to solve a problem caused by having separate tables for books and ebooks. The problem would not have existed if you had used only a single table for books and ebooks. You can fix a problem when you discover it or you can make a patch and move on.

 

[LadyoftheForest]

I didn’t come here because I had “problem” with separate tables. I did not want the information lumped together, I know how to do that.
I was trying to learn how to add two distinctly different subforms, that drew information from each of those different tables, to a tab. Any future features that might possibly be added, will also be distinctly separate from either of the tables.
I do truly understand best practices, but that did not suit my needs in this instance. Arnelgp understood exactly what I was trying to achieve. But again, I do thank you for your insight.

I want to add to the Flower Details form, a place (I think it should be subform) to be able to pull from the Book titles table, similar to an order list and instead of a quantity, it lets me add a page number, several numbers or a range of numbers for that plant found in the book, probably as short text. It only needs to show the Title, ShelfLocation and then the short text page number column.

I then would like to have a second “order” subform that does the same for the EbooksList. This one would only need to pull the Title and Hyperlink to the PDF file from the Ebooktable, I don’t need page numbers because they are searchable.

 

[Pat Hartman]

I do truly understand best practices, but that did not suit my needs

I can't argue with that.