modal form can't add new record?

[AccessIdiot]

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!

[AccessIdiot]

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 :-)

[NeoPa]

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?

Call DoCmd.GoToRecord(Record:=acNewRec)

[AccessIdiot]

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?

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?

[NeoPa]

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.

[NeoPa]

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.

[AccessIdiot]

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 :-)

[AccessIdiot]

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:

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?

[NeoPa]

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.

[NeoPa]

Snap!
I'll answer your latest post now - just to let you know I'm aware of it.

[NeoPa]

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:

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.

[NeoPa]

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.

[AccessIdiot]

lol

Okay, I got it into a CLASS module (sorry about that). Now it's barking at this line:

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:

Private Sub cmdGroupCust_Click()
    Call clsTo.ShowForm(strTo:="frmGroupCust")
End Sub

does frmGroupCust refer to a specific form name?

thanks for your patience . . .

[AccessIdiot]

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!

[NeoPa]

lol

Okay, I got it into a CLASS module (sorry about that). Now it's barking at this line:

Call ShowMsg(strMsg:=conUnInitMsg, strTitle:="classForm.ShowForm")

"Compile error: Sub or Function not defined"

Replace with :

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:

Private Sub cmdGroupCust_Click()
    Call clsTo.ShowForm(strTo:="frmGroupCust")
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.

[NeoPa]

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.

[AccessIdiot]

Okay now I'm getting the same error message on

    Call ErrorHandler(strName:=strTo, _
                      strFrom:=frmFrom.Name & ".ShowForm", _
                      lngErrNo:=Err.Number, _
                      strDesc:=Err.Description)
 

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!

[NeoPa]

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.

[AccessIdiot]

awesome - thanks so much for your patience!

[NeoPa]

{1} Okay now I'm getting the same error message on

Public Function ShowForm(strTo As String) As Boolean
    ShowForm = True
    'Don't even try if caller hasn't initialised Form object yet
    If Uninitialised() Then
        ShowForm = False
        Call MsgBox(conUnInitMsg, , "classForm.ShowForm")
        Exit Function
    End If
    Call DoCmd.Restore
    'Handle error on OpenForm() only.
    On Error GoTo ErrorSF
    Call DoCmd.OpenForm(strTo)
    On Error GoTo 0
    Set frmTo = Forms(strTo)
    frmFrom.Visible = False
    Exit Function
 
ErrorSF:
    ShowForm = False
    Call ErrorHandler(strName:=strTo, _
                      strFrom:=frmFrom.Name & ".ShowForm", _
                      lngErrNo:=Err.Number, _
                      strDesc:=Err.Description)
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.

[NeoPa]

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.

[AccessIdiot]

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.

[AccessIdiot]

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.

[NeoPa]

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.

[NeoPa]

Check it again as I've now added the optional varOpenArgs parameter so that you can pass that if needed.

[AccessIdiot]

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?

[AccessIdiot]

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?

[AccessIdiot]

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 :-)

[NeoPa]

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?

[AccessIdiot]

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!

[NeoPa]

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.

[AccessIdiot]

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 :-)

[NeoPa]

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.

[AccessIdiot]

Of course! No guarantees - I would never ask for anything more than a look. :-)

Cheers!

[NeoPa]

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.

[NeoPa]

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.