belated followup

[junkmale]

quote:
--------------------------------------------------------------------------------
Originally posted by namliam
To Add to a dropdown list, add a popup form On double click or On not in list or add a small button behind it. Then open a form, where the user can input his data on the (new/unknow) publisher.... Then return to your 'main' form...

Regards
--------------------------------------------------------------------------------

This is a belated followup from an old thread posted in "tables"
http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=54357&perpage=15&pagenumber=1

Realised maybe nobody would bother to read the whole chunk so i try to make it shorter and to the point here.

Basically i require a "dropdown menu" to enter information for a foreign key(publisherID) in my form for Books. The problem arises when the book being entered into the database is from a "new publisher", which cannot be found in the pull-down field.

----

Hi there again, sorry, but need more help.
probably elementary to you, but i've wasted hours trying to get it to work.
I've tried to do what you've said above, to try to add a popup form On double click or On not in list. but I can't quite get it to work. What exactly is suppose to happen? Will there be an extra line in my dropdown "Not in list" which i select and a form pops out? Could you give me the maybe some more specific instructions or code to make it work?

Alternatively, I've added a button which simply opens the formublisher. The problem with this solution is that after keying in the information for PublisherID:108, and closing this form, the the information of PublisherID: 108 is not updated in my dropdown list for the first form. That is to say I still cannot select this new publisher. I have to close the form and re-open it for the publisher to be available for choosing. How do i solve this?

Looking forward to your reply!

 

[FoFa]

Requery your control (drop down list) after adding the new publisher.

 

[junkmale]

Requery your control (drop down list) after adding the new publisher.

thanks... i've gone to try and do a requery through a macro,
and for simplicity placed it as a button to be clicked "[refresh
]". it works this way, but....

do you have any suggestion where to place the requery to make it seem like it "auto-refreshes" after i come back to the form from exiting the Publisher form???


a seperate problem,
and also, when i click the button to add new Publisher, the form emerges displaying the first entry of my publisher table... ie publisherID:1.... is there something i could add to the action to make it auto open a "fresh" form to be filled up?

 

[namliam]

*GRMBL* dont open a new thread AND have the same question in the original thread !!!

*SIGH*

 

[junkmale]

*GRMBL* dont open a new thread AND have the same question in the original thread !!!

*SIGH*

really sorry, but after posting in the old thread i started thinking it was a little silly to get others to read the whole thread just to answer a question at the very end of it.

also

To show your new publisher add a requery of the combobox after closing the popup form....

Besides having a module to handle the not in list event you must also set Limit To List to yes, otherwise the not in list event will not be triggered...

Hope that helps you

Regards

like previously, i had added a button to do the "requery"... is there a better place for me to "place" this requery so others do not need to do anything extra for the requery to be done?

i was wondering if i could do a requery upon exit of the popup form. i am thinking of having requery, ON EXIT, in the Publisher form. Since I'm requerying something in another form, what should i type as CONTROL? previously i simply had "Publisher"

For now i have an event for opening formublisher set as selected as the event for On Not in List. I've also set Limit to List as yes. But I'm still unable to trigger the not in list event. What action is it exactly, that is suppose to trigger the event? And just in case my code was wrong in the first place, can i have the basic syntax for launching another form?

looking forward to your reply.

 

[namliam]

Try opening the popup form as modal or popup (i forget which one) --- edit: I meant Dialog ---

In any case it should pause your code untill the form is closed... then you can put the requery right below the openform on the same form as the control...

Meanwhile it also limits the user to only that screen....

Try testing it with a msgbox...

Regards

 

[FoFa]

Typically what I do (not saying it is right) is set a invisible flag (usually a text box) to a value then open the new form (which you should move to a new record for input so you don't display the old data). In the activate event of the original form I check if that flag is set, if it is I requery the control and reset the flag. That way when they close the ADD xxx form, and return to the main form, it will requery for you. Basically it would be like this.

ON CLICK ADD xxx BUTTON
SET FLAG to A
OPEN ADD FORM

ON ACTIVATE Main Form
IF FLAG is A THEN
Requery MyControl
SET FLAG to N
END IF

 

[namliam]

FoFa,

Sure it works, but i think that the powers that 'be' would say that its "No good"

Your executing code when its not needed, where its only needed after adding in the popup screen...

---From memory---
sub someClick
docmd.openform "myformname",,,acdialog
me.mycombo.requery
end sub

Mind you FROM MEMORY, anthing might be wrong play with it....

Regards

 

[junkmale]

Try opening the popup form as modal or popup (i forget which one) --- edit: I meant Dialog ---

In any case it should pause your code untill the form is closed... then you can put the requery right below the openform on the same form as the control...

Meanwhile it also limits the user to only that screen....

Try testing it with a msgbox...

Regards

Is this what you mean? it doesn't quite work...
something i did wrong?

Private Sub Command17_Click()
On Error GoTo Err_Command17_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Publisher"
DoCmd.OpenForm stDocName, , , stLinkCriteria

DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

Exit_Command17_Click:
Exit Sub

Err_Command17_Click:
MsgBox Err.Description
Resume Exit_Command17_Click

End Sub

ON CLICK ADD xxx BUTTON
SET FLAG to A
OPEN ADD FORM

ON ACTIVATE Main Form
IF FLAG is A THEN
Requery MyControl
SET FLAG to N
END IF

I can understand what you're trying to do, and it seems like it could work, but i have absolutely no idea how to go about doing that...

 

[namliam]

Private Sub Command17_Click()
On Error GoTo Err_Command17_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Publisher"
DoCmd.OpenForm stDocName, , , , , acDialog
Me.MyComboBox.Requery

Exit_Command17_Click:
Exit Sub

Err_Command17_Click:
MsgBox Err.Description
Resume Exit_Command17_Click

End Sub

Something like above....

Regards

 

[junkmale]

that worked!

Thanks!

hmm, just a smaller question on the side, what can i add to the code to make sure when the form opens, it shows a "fresh form"? (new record). For now, it just shows the first entry, usually publisherID 1.

And also, can i do something so that the newly entered publisher is selected automatically, such that i do not need to select the publisher from the list?

 

[namliam]

docmd.OpenForm ,,,,acFormAdd,acDialog

Will open the form in "add" modus...

Secondly, hoping that PublisherID is an autonumber, you might try adding something like

me.myComboBox = Dmax([publisherID],[PublisherTable])

After the requery offcourse....

Regards

 

[junkmale]

Thanks...

acFormAdd works, but presents a new problem.

my ID is not an autonumber, but simply a number, for the person to key in. with FormAdd, I have no idea which IDs are already taken, as the information bar at the bottom shows it to be the first record. [previously, if it shows as 17th record, i will use 17 as ID. ]

Should i change to autonumber for ID? I've heard of people having trouble with Autonumbers as ID, which persuaded me to use a "manual" number.

or is there someway around this?

 

[namliam]

Any table should have a true PK. In most instances (like this one) use an autonumber for that purpose....

The number doesnt have a real meaning (to your or the user(s)) other than beeing a link to a publisher. Dont even show it to the user, but make it an autonumber.

What problems are you refering to? The most common problem is peoply trying to give meaning to an autonumber, where an autonumber simply shouldnt have a meaning. Its a number, usable for the DB ONLY !

Regards

 

[junkmale]

I think in this case, ID is actually useful to the user, because for books, the combination of BookID/CopyID is actually going to be the unique identifier for any physical book found in the library(bcos the library will be holding multiple copies of some books). Refering to this call number instead of the book details, eg title, author, will be easier for the librarian when processing loans, etc. This is especially when there are also multiple books with the same generic title, eg Economics, World Atlas, etcetc, but are in fact different books.

one of the problems with autonumber is the fact that if any entries(books) are removed, that number will never be used again. eg if BookID 3 is removed from the library, i cannot have a new book with ID 3. Not an immensely negative point though.

But anyway, i'll be backing up my work and trying to convert to autonumber for my IDs and see if they actually bring up any problems.

Thanks again for your help!

 

[namliam]

Correct me if i am wrong... but were we talking about PublisherID or BookID?

And yes the "Lost" ID does not get used again. But that is for a reason, FK's may still be using it.... Also re-using keys in general is not a good idea. Take a bankaccount for instance. If you cancel yours the account is not used for several years! Just in case some where your account is still "floating" around somewhere....

Regards

 

[junkmale]

Correct me if i am wrong... but were we talking about PublisherID or BookID?

yes, we were talking about publisherID.
but my initial decision not to use autonumbers was based on the bookID issue i talked about, and i actually did it for everything else for consistancy.

Anyway, i've changed all to autonumbers and there appear to be no problems... as of yet. hope it lasts.

And yes the "Lost" ID does not get used again. But that is for a reason, FK's may still be using it.... Also re-using keys in general is not a good idea. Take a bankaccount for instance. If you cancel yours the account is not used for several years! Just in case some where your account is still "floating" around somewhere....

Regards

I get what you mean by that that foreign keys might still be linking to it. Would not be good if i open a new bank account and someone else's electricity bill gets deducted off it every month!

But the problem should not be too great in this case, as the worse i can imagine is borrower's records getting messed up, ie the guy borrowed "The 3 little pigs" but when he checks back, it seems like he borrowed "How to make WMDs", because the second book took the ID previously used by the first book.

Nonetheless, i've converted to autonumbers. Thanks for your help along the way!

 

[namliam]

There is no need to use just one methode thru-out your DB if you need autnumbers in a table use it if you dont, dont use it and have your own (genereted or user entered) key...

I have even seen setups where they have both autonumbers and user key values. Then use the Autonumber as the DB PK but the user key as visible....

This is hard to maintain.... but it works non the less.... Keys are KEY (pun intended) one of the most important thing of any DB is to get your PK's and FK's (as well as the relations) correct. Once you have that, all else seems trivial....

Regards

 

[junkmale]

Private Sub Command17_Click()
On Error GoTo Err_Command17_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Publisher"
DoCmd.OpenForm stDocName, , , , , acDialog
Me.MyComboBox.Requery

Exit_Command17_Click:
Exit Sub

Err_Command17_Click:
MsgBox Err.Description
Resume Exit_Command17_Click

End Sub

Something like above....

Regards

sorry, but having some problems with this again... it had worked fine for my drop down list to select a certain publisher for a book.

can i requery for a field in a subform?

basically, what i tried was this:

Me.[BookAuthor subform].Requery

no error msg, but nothing refreshed also.

then, i also tried for a variety of playing around with the code...
[BookAuthor subform.AuthorID].Requery
Me.[BookAuthor subform.AuthorID].Requery
etcetc

none of the combinations of these work...

how exactly do i requery a field/control in a subform?

i've included the relevant tables and forms here. The important form in question is "Books".

 

[namliam]

Me.[BookAuthor subform].[AuthorID].Requery

Did you try something like above?

Regards

P.S. Was unable to look at your sample for now... sorry... and provided ' solution ' is AIRWARE ...