Solved Form - Subform Conflict

[Romio_1968]

I attached a database called Bookstore

By now, I have a main AddTitle form and a subform that should add author (not finished yet, but working so far)
Without the subform, the main form works as it should (at this time, again).
Yet, after the sub is embeded, the main does not work anymore.

Problems:
On opening the main Add_Title_Frm form, the Title_ID control labeled Cod Tiltu is filled with the new Title_ID record from Titles table (Autonumber), as it sould
It allows me to fill all the data in the main form
When I am moving (getting focus) to the sub form, the data from the mai suddenly get messed:
All fields except Call_No (label Cota) and DomainCombo_1 (label Domeniu principal) are wiped out and the Title_ID control indicates (New)

Annother thing, wile working with the forms in design view, sometimes a new empty record is added to Title table

Please help me debugging this.
Thank You

 

[arnelgp]

you try and test.

 

[MsAccessNL]

There are quite some hits, also youtube, when you google Access Library database. Access has also a working Bookstore/library template, but probaly with macro's in it.

Redirecting

Shows a table relationship with a download.

I think your subform is linked by id field to the mainform,, so if the id changes on the mainform, the subform will change to. Always try to work with example data when developing.

 

[Romio_1968]

Tested

Works pretty well

So. I can add a new author via the subform, or I can choose one that is already in list > Excelent
Also fills the Junction Table > Good

It seems that it will save the record even if the PublishYear_Box is not filled. That is not good. It is a mandatory field
I tested the behavior on differen other unfield mandatory controls. there is something fishy
I filled the Format, then titlle. Tried to move on sub and a nice message comes out.
Now, if I delete the title and go again on sub, the message comes again, but the Format becomes red, even if it is filled (only the relation between the Format and Title controls.
If I am filling all the main form fields an pass to the subform, i can load authors, as I said.
But if i am deleting (for testing reason) the publisher (editura) field after adding data to the sub, it saves the record with a unfilled mandatory field. That is not good

After saving the record, the control labeled Cota remains dirty

You added a form called newAuthor. What is the purpose of that?
The old Save button should ne discarded?

Thank you for all your help

 

[arnelgp]

PublishYear_Box field is Already filled, with 0.
so to check use:

If Nz([PublishYear_Box], 0) = 0, on your "save" button.

Create a Relationship between Title table and TitleAuthor_Junction table.
Set Cascade Delete Related records.
so when you delete from the Main form, all related records on the subform are also deleted.

 

[Romio_1968]

Comming back with testing

Save button actualy saves the record, but does not jump to a new record after saving.
Why it should be keeped, since the New button saves and goes to new record. Isn't it redundant?
I tried to remove it and I get an OnLoad error.
Can I load its functions inside the new button?

There is a relationshib between Title and TitleAuthor_Junction tables

The cascaded delition is not set... kind of being afraid
I don't want that the delition of a Title to erase the authors form the Author Table
If i set cascaded deletion on Title > Junction and no Cascade on Author > Junction, will work?

 

[arnelgp]

If i set cascaded deletion on Title > Junction and no Cascade on Author > Junction, will work

it will work this way:
when you delete from Titles table, all related table to TitleAuthor_Junction will be deleted. Author table is not affected.

 

[Romio_1968]

If I am filling some data on the ComboBox and delete it afterwards, the error msg>
You tried to assign the Null value to a variable that is not a Variant data type.
pops out.
There is no way to leave the combo on the prior empty state.
How should I fix that?

 

[arnelgp]

if the Record is already saved, you don't erase the value on the combo.
you Delete the record itself.

 

[Romio_1968]

Ok... let me be more clear
I am selecting in the Combo some data from the list.
For some reason, i change my mind and delete the value in the Combo (just empy the content of the Combo), so now it shows nothing selected.
When I am trying to leave the combo, thet message pops and all is blocked. I cand leave the box, leave the record, leave the form... I am trapped in that error.
The Null value inside that combo prevents me to abort, so I must select a name.
Even if I am adding some author name trough the combo, so in Authors table will now exist a record, I should be able to change my option and abort the process of adding an entry in the junction table.
Moreover, If I am adding some authors (existing or new) I should be able to remove some of them before saving the data in the Junction Table

So I need something to read the content of the Combo before ubdating and Undo if is null (so I can leave the Combo) and a button or something asside the Combo to help me delete the entry from the Junction table.

 

[arnelgp]

if the author record is not yet Saved, you press ESC key (or many times) to Undo the combo/record.

here also is a your db that has a Delete button.
on your Main form, you should also add a delete button if you are not showing
the Record Selector.
also suggest that you Enable the Navigation Buttons on the Main form.
otherwise, create a button for your Navigation to each records.

 

[Romio_1968]

The button woks nice, Thank You
Still, even if I notticed that pressing Esc is untrapping me, somehow I have to prevent that error, or, I can use a sticky note on the monitor to let my coleagues to press the Esc

 

[Romio_1968]

Actually the button is not working
If I open the subform itself, it has some reaction. In the main form doesn't work

Fixed (i gues)
It wasn't If Not Me,Newrecord, but If Me.Newrecord.
(Yes, it works... the condition was wrong)

 

[Romio_1968]

How can I prevent entering the same author more then one time for a title?
Yet, that button still does not do his job

For one author works fine
For more then one, it stops working

The behavior is this:
Adding one>

I can delete Jimmy Jones
If i press on the button near the second row (the empty one) it runs into an error:

This should be trapped

Adding two>

If I hit row 3 button, goes to 2046 error
If I click on second row button, it works, bu the row one reccord cannot be deleted anymore (probably because it is writte in the Junction table).
And as you can see, I am able to add Jimmy twice. Usualy, twice is better, but not in this case


,

 

[arnelgp]

delete all records from your table and start over again.
i also suggest, you grab any books about ms access programming.
you can't create something without any general knowledge of msa.

and i am really surprise that you are doing Trial and error.

 

[Romio_1968]

"and i am really surprise that you are doing Trial and error."
How else? You told me to test it, I am testing it
(somebody just went angry because I removed some of his doings just because they didn't work as I wanted to, so ... I am testting now)
Probably I don't have enough general knowledge of MSA, yet I understood quite well that that button does not do the intended job, data being added into the table by the Combo and not being removed after the button use.
Thought you may be interested in the result of your work since you asked for testing

Thank you for your hep so far

 

[Romio_1968]

arnelgp, maybe you remember this code
just for a roadmap, hoping that you will not send me back to some general kowledge MSA crash course

In the new database, I will create a new table, named Inventory
It wil have 3 fields: InvenoryID, BookID_FK, Inventory_No
I will create a one to many relationship between the Title and Inventory tables (BookID as primary in Titles and BookID as foreign in Inventory)
Inside Add_Title form i will put an unbound control that will retrive the last inventory No. from the Inventory table (locked for editing) and a new one be filled with the number of copies.
When saving the title, the two values will be used to add records into Inventory table.

Do you think it is a good approach?