By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,986 Members | 2,042 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,986 IT Pros & Developers. It's quick & easy.

modal form can't add new record?

AccessIdiot
100+
P: 493
Okay I JUST learned about modal.

I have two forms. Form 1 opens form 2. I want Form 1 to be "locked" until the user is done with form 2. So I set the modal property on form 2 to "true". This works great, except that I want to be able to add as many records as I want using form 2. Once I'm done with form 2 I want to close it and have Form 1 be "unlocked".

What do I have to do?

thanks for any help!
Mar 9 '07 #1
Share this Question
Share on Google+
35 Replies


AccessIdiot
100+
P: 493
So upon further research I guess I can't use a button to go a new record if the form is Modal. Hmmm, how to get around the problem? Is there something like modal that deactivates form 1 until the user is done with form 2 (including adding multiple records)?

thanks!
melissa :-)
Mar 9 '07 #2

NeoPa
Expert Mod 15k+
P: 31,347
Is form 2 in datasheet view or is it a single record to view?
What happens if you try setting Me.Recordset to the new record in the AfterUpdate event of the form?
Expand|Select|Wrap|Line Numbers
  1. Call DoCmd.GoToRecord(Record:=acNewRec)
Mar 10 '07 #3

AccessIdiot
100+
P: 493
Is form 2 in datasheet view or is it a single record to view?
What happens if you try setting Me.Recordset to the new record in the AfterUpdate event of the form?
Expand|Select|Wrap|Line Numbers
  1. Call DoCmd.GoToRecord(Record:=acNewRec)
Hi,

Unfortunately the code didn't work - I still get the message "Can't go to specified record".

The form is in single record view.

Is there any code I can post or anything to help?
Mar 12 '07 #4

NeoPa
Expert Mod 15k+
P: 31,347
Unfortunately I haven't used modal forms so was unaware of their limitations.
I have a class module that I use that enables me to open form in a structural manner (A opens B - A is hidden until B is closed).
In case you're interested, I'll post in the code section with instructions on usage then, when done, I'll come back here and post a link.
Mar 12 '07 #5

NeoPa
Expert Mod 15k+
P: 31,347
Cascading Forms. is where the new code has been posted.
It may not be what you need, but there again, it may be. I always use it to keep my Access database interface clean and under control.
Mar 12 '07 #6

AccessIdiot
100+
P: 493
Wait, there's an Access Code forum? Sweet! :-)

This sounds right up my alley (locking a form until another form is closed) but the code looks way over my head. I am going to have to spend some time looking at it.

Thanks for posting it - I know it will come in handy for many that are more savvy than me and hopefully I can use it!

In case i can't though - would it make more sense to include form2 inside form 1 as a subform and then lock the main form until a button is pressed in the subform?

Or is that just really ugly and clunky?

I'm trying to find/use a method that a newbie like me can handle, edit, and perhaps even reuse.

cheers,
melissa :-)
Mar 12 '07 #7

AccessIdiot
100+
P: 493
Okay I'm not sure about all of it but I'm going to dive in and give it a try.

I created a new Module and called it classForm. Then I pasted the code you wrote but it is turning this line red and throwing an error message:
Expand|Select|Wrap|Line Numbers
  1. Private WithEvents frmCalled As Form
"Compile error: Only valid in object module"

And now for my first really dumb newbie question: where do I put the 2nd set of code? Does it go on the 2nd form?
Mar 12 '07 #8

NeoPa
Expert Mod 15k+
P: 31,347
Melissa,
It's frequently been my experience that once people get over the "OMG I could never understand that" moment and just try it out, they find that it was only confusing because they hadn't tried it.
It may be a little advanced to create such a concept, but to use one already designed and implemented is surely not. I may be proven wrong, but I'd like for you to give it a go if you wouldn't mind. See how it works. Can you get it to work even?
One of the benefits of a class like this is that it's infinitely cascading and can handle a structure of multiple called forms without any extra coding complexity.

Remember, I can always explain away any difficulties you may have.
I also have a similar one for reports, but if no-one can get it to work I won't bother posting it.
Mar 12 '07 #9

NeoPa
Expert Mod 15k+
P: 31,347
Snap!
I'll answer your latest post now - just to let you know I'm aware of it.
Mar 12 '07 #10

NeoPa
Expert Mod 15k+
P: 31,347
Okay I'm not sure about all of it but I'm going to dive in and give it a try.

I created a new Module and called it classForm. Then I pasted the code you wrote but it is turning this line red and throwing an error message:
Expand|Select|Wrap|Line Numbers
  1. Private WithEvents frmCalled As Form
"Compile error: Only valid in object module"
You need to create a new CLASS module.
And now for my first really dumb newbie question: where do I put the 2nd set of code? Does it go on the 2nd form?
It goes on both forms, as explained following :
Points A, B & C need to go in any form that wants to call another form.
Points D & E need to go in any form that is called by another form.
I don't have any forms that don't have points D & E in them but the real criteria is do they get called by other forms using this class.
Mar 12 '07 #11

NeoPa
Expert Mod 15k+
P: 31,347
One of the nice things about this class is that any forms that are designed (modified) to be called in this way can still be called quite happily in the ordinary way without any adverse affects. Even simply opened from the main database window.
Mar 12 '07 #12

AccessIdiot
100+
P: 493
lol

Okay, I got it into a CLASS module (sorry about that). Now it's barking at this line:
Expand|Select|Wrap|Line Numbers
  1. Call ShowMsg(strMsg:=conUnInitMsg, strTitle:="classForm.ShowForm")
"Compile error: Sub or Function not defined"

I'm sure I could make something up but I'm equally sure it would be wrong. :-)

I am distributing A-C and D-E now . . .
nope - got a question first - is there somewhere that I have to change form names? For example this bit:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdGroupCust_Click()
  2.     Call clsTo.ShowForm(strTo:="frmGroupCust")
  3. End Sub
does frmGroupCust refer to a specific form name?

thanks for your patience . . .
Mar 12 '07 #13

AccessIdiot
100+
P: 493
Okay I tried it anyway and it didn't work. I'm sure it's something obvious but I was able to access and change form 1 while form 2 was open. Also, when I try to advance to a new record in form 2 (I have a button set up to do this) I get that error message that I can't go to the specified record.

thanks for any help!
Mar 12 '07 #14

NeoPa
Expert Mod 15k+
P: 31,347
lol

Okay, I got it into a CLASS module (sorry about that). Now it's barking at this line:
Expand|Select|Wrap|Line Numbers
  1. Call ShowMsg(strMsg:=conUnInitMsg, strTitle:="classForm.ShowForm")
"Compile error: Sub or Function not defined"
Replace with :
Expand|Select|Wrap|Line Numbers
  1. Call MsgBox(conUnInitMsg, , "classForm.ShowForm")
I will fix it in the code posted.
I'm sure I could make something up but I'm equally sure it would be wrong. :)
Good instincts - it would also leave us out of synch - a worse result.
I am distributing A-C and D-E now . . .
nope - got a question first - is there somewhere that I have to change form names? For example this bit:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdGroupCust_Click()
  2.     Call clsTo.ShowForm(strTo:="frmGroupCust")
  3. End Sub
does frmGroupCust refer to a specific form name?
Yes it does. That is one of my forms. You would change both the procedure name and the name of the form in your code.
Mar 12 '07 #15

NeoPa
Expert Mod 15k+
P: 31,347
Okay I tried it anyway and it didn't work. I'm sure it's something obvious but I was able to access and change form 1 while form 2 was open. Also, when I try to advance to a new record in form 2 (I have a button set up to do this) I get that error message that I can't go to the specified record.

thanks for any help!
I can't help you with the specifics of this one Melissa.
I was just hoping to provide a version of modal forms without any inherent restrictions.
Mar 12 '07 #16

AccessIdiot
100+
P: 493
Okay now I'm getting the same error message on
Expand|Select|Wrap|Line Numbers
  1.     Call ErrorHandler(strName:=strTo, _
  2.                       strFrom:=frmFrom.Name & ".ShowForm", _
  3.                       lngErrNo:=Err.Number, _
  4.                       strDesc:=Err.Description)
  5.  
Also, I was just explaining what wasn't working in case it had something to do with the code you suggested.

Is "cmdGroupCust_Click()" the click event of a button called cmdGroupCust?

thanks again!
Mar 12 '07 #17

NeoPa
Expert Mod 15k+
P: 31,347
I can't help you with the specifics of this one Melissa.
I was just hoping to provide a version of modal forms without any inherent restrictions.
I'm sure if we can get the cascading forms to work we can look at your other problems then and find a solution for you.
Mar 12 '07 #18

AccessIdiot
100+
P: 493
awesome - thanks so much for your patience!
Mar 12 '07 #19

NeoPa
Expert Mod 15k+
P: 31,347
{1} Okay now I'm getting the same error message on
Expand|Select|Wrap|Line Numbers
  1. Public Function ShowForm(strTo As String) As Boolean
  2.     ShowForm = True
  3.     'Don't even try if caller hasn't initialised Form object yet
  4.     If Uninitialised() Then
  5.         ShowForm = False
  6.         Call MsgBox(conUnInitMsg, , "classForm.ShowForm")
  7.         Exit Function
  8.     End If
  9.     Call DoCmd.Restore
  10.     'Handle error on OpenForm() only.
  11.     On Error GoTo ErrorSF
  12.     Call DoCmd.OpenForm(strTo)
  13.     On Error GoTo 0
  14.     Set frmTo = Forms(strTo)
  15.     frmFrom.Visible = False
  16.     Exit Function
  17.  
  18. ErrorSF:
  19.     ShowForm = False
  20.     Call ErrorHandler(strName:=strTo, _
  21.                       strFrom:=frmFrom.Name & ".ShowForm", _
  22.                       lngErrNo:=Err.Number, _
  23.                       strDesc:=Err.Description)
  24. End Function
{2} Specifically the first line is highlighted in yellow but the ErrorSF: line is highlighted in grey. Not sure what that means.

{3} Also, I was just explaining what wasn't working in case it had something to do with the code you suggested.

{4} Is "cmdGroupCust_Click()" the click event of a button called cmdGroupCust?

thanks again!
Quick point to make first - I changed the ErrorHandler call too as that is specific to my code (in my library).
  • What's the error message you're getting?
  • When a procedure definition line is in yellow it means that there is something so wrong in the procedure it can't be compiled. I would need the error message to know what is wrong with the ErrorSF: line though. It seems fine.
  • I see. I doubt it in this case, but as I say, we'll get on to that.
  • Absolutely! Spot on.
Mar 12 '07 #20

NeoPa
Expert Mod 15k+
P: 31,347
This overlapping of posts is going to get confusing :confused:
I replied to your post before you got the edit in I'm afraid. Please let me know if there is anything still outstanding after my last post.
Mar 12 '07 #21

AccessIdiot
100+
P: 493
Sorry - I edited my post while you were responding! I closed everything and reopened it and compiled the code. It was getting stuck on the Call ErrorHandler function (there isn't one defined?) and giving me the error message "Sub or Function not defined".

Am fixing it now.
Mar 12 '07 #22

AccessIdiot
100+
P: 493
Okay, it seems to be working the way you had intended. :-)

Unfortunately, it won't work for my project as is. See, there is a variable that gets passed in the OpenArgs part of the open form statement on my current button that goes from form 1 to form 2. It populates a disabled field I have on form 2. Every record of form 2 should hold this value until the user closes form 2 and changes the value on form 1.
Mar 12 '07 #23

NeoPa
Expert Mod 15k+
P: 31,347
Melissa,
I need to go to bed now (Early I know, but I'm a little under the weather).
I will come back to this tomorrow and may even update my code to handle the OpenArgs parameter. I never use it in my code so I didn't implement it.
It's easily enough done though. Just add an extra parameter to the ShowForm() function as well as the line that opens the form within that function.
My routine certainly needs that added if it's to be general purpose as it should be really.
Mar 12 '07 #24

NeoPa
Expert Mod 15k+
P: 31,347
Check it again as I've now added the optional varOpenArgs parameter so that you can pass that if needed.
Mar 13 '07 #25

AccessIdiot
100+
P: 493
Hi,

Sorry, I was out sick yesterday.

That works great! Especially once I figured out that I had to add the parameter to the showForm call in section C. :-)

However, I'm still getting the dreaded "Can't go to specified record" error message when I try to add another record in the 2nd form.

Any ideas?

Also, none of the records are being added to the database?
Mar 14 '07 #26

AccessIdiot
100+
P: 493
For what it's worth, here is my complete setup. Maybe it will help or maybe it will just confuse but I'll outline it anyway.

I have a main form that has 3 buttons. Each button opens the same form but passes a different string to the form. When a button is clicked the main form is closed to prevent the user from pressing another button. The form that is opened is form1. The string that is passed from the button is concatenated with an integer that the user enters in an unbound textbox on form1 and that value is put in the table that feeds form1.

There is a button on form1 that launches form2. The concatenated field from form1 should also show up on form2 (getting passed by OpenArgs) and be entered into the table that feeds form2 (they are linked by a one to many).

There is a button on form2 that adds a new record. The user should be able to add as many records as they like, keeping that concatenated field that was passed from form1. When they are done, they should be able to click on a button that closes form2 and "unlocks" or makes active form1 again. Now the user can change the number in the integer field, which is concatenated with the string passed from the main form, click a button to open form2 again, and start over with the new concatenated value.

Eventually the user may want to click on the button on form1 that closes form1 and reopens the main form so they can click on different button there which passes a different string to form1 and it starts all over.

I hope this isn't too confusing. If it would help to draw a picture I can do that if you tell me how to upload attachments.

If it doesn't seem like the code you are helping me with is suitable for this type of project I can try something else like making form2 a subform of form1 and enabling it with a button that also locks the form when the subform is active?
Mar 14 '07 #27

AccessIdiot
100+
P: 493
I'm sorry NeoPa but I am going to have to abandon this. I have to have something to show the folks who are paying us to create this project and I am going to have to stick with something I can fumble my way through, instead of relying on you to explain your code. I wish I were better at VBA, so I could understand and work with this great class you have written, but unfortunately I'm just not that savvy.

Thank you for all the wonderful time and attention and I sure do hope this thread helps someone in the future!

cheers,
melissa :-)
Mar 14 '07 #28

NeoPa
Expert Mod 15k+
P: 31,347
Sorry about that Melissa.
I can understand time pressures and I'm grateful you tested it out. It helped round off some edges at least.
I'm afraid I can't really commit or guarantee my time on here. I try to make available what I can but I can't always predict what time I'll have available :(

I thought you had it working - so I'm a little confused. I know you still had difficulty with the rest of the problem. Let me know how (or if) you want to proceed. Do we just give up as a bad job, or are you still looking for assistance?
Mar 15 '07 #29

AccessIdiot
100+
P: 493
I'm afraid I can't really commit or guarantee my time on here. I try to make available what I can but I can't always predict what time I'll have available :(
This is exactly why I have to abandon this (although I seem to be struggling no matter which method I use). Your code is above and beyond my range of capabilities - I can't troubleshoot when something doesn't work. So I have to rely on you to bail me out and you have been so patient and wonderful but I know you are helping others too and I don't want to take up so much of your time.

I thought you had it working - so I'm a little confused. I know you still had difficulty with the rest of the problem. Let me know how (or if) you want to proceed. Do we just give up as a bad job, or are you still looking for assistance?
It was working to an extent, but there is so much more I need to be able to do. I hate to give up but I just don't know how to proceed without major help.

Thanks again NeoPa, I really do appreciate everything you've done for me!
Mar 15 '07 #30

NeoPa
Expert Mod 15k+
P: 31,347
I know how frustrating that can be :(
Would sending the database in e-mail for me to have a look at help maybe? It may be possible to resolve quickly that way. I could only do that sort of work in the evenings at home of course, but it may be of some use. Let me know if you think this may be an idea.
Mar 15 '07 #31

AccessIdiot
100+
P: 493
I would LOVE to do this if you feel you have the time. Feel free to email or PM me and I will send you the db.

Thank you so much!
melissa :-)
Mar 15 '07 #32

NeoPa
Expert Mod 15k+
P: 31,347
I can't promise I can fix your problem, but it will certainly be much easier if I can see the whole db on my own system.
I'll PM the details.
Mar 15 '07 #33

AccessIdiot
100+
P: 493
Of course! No guarantees - I would never ask for anything more than a look. :-)

Cheers!
Mar 15 '07 #34

NeoPa
Expert Mod 15k+
P: 31,347
I've sent the PM and I'm still at work atm so I'll have to wait a while before I can even open it.
I hope to be in touch later this evening though - barring unexpected circumstances.

-NeoPa.
Mar 15 '07 #35

NeoPa
Expert Mod 15k+
P: 31,347
When I get some time at home, I'll try to post all the recommendations (Not the whole discussion - That would wear my fingers to the bones) that will probably affect the new status of the database for when we continue in here. So any third party will know as much as possible about what's happened and can follow the thread more easily.
Mar 16 '07 #36

Post your reply

Sign in to post your reply or Sign up for a free account.