Problem with a Button and a Subform 1

[Myvee]

Hello - I'm hoping someone will be able to answer the following question I have regarding a form I am working on. I have been trying for days, with varying success to get this to work - I've tried everything I can think of but I'm missing something.... Here is a description of what I'm working on and what I would like it to do:

I have a main form which displays information from a query(no data input here)with a subform where users enter data in up to 12 fields (there is code which opens the form with the exact number of fields specified by the user). The main form contains a command button which closes both forms and opens another.
The fields in the subform must contain data. I know there are a variety of ways to validate data but what I want to do is put this action into the command button. I want to click the command button, validate for a "" condition in the subfields, send a message if data is missing and cancel the closing of the form. So far I can accomplish all but one thing using a macro. I can't stop the button from closing the form before the user has had a chance to fill in the data.. I'm not sure what i should do here...any suggestions?
Thank you!

 

[ItIsHardToProgram]

I think you should look at the forms afterupdate or beforeupdate or close properties and like this

private sub Formname_beforeupdate(cancel as integer)

if the if statement is activated, cancel = true

end sub

that is what i used

 

[Myvee]

Hi ItIsHardtoProgram:

Thanks for your quick response - are you referring to the main form or the subform's before update etc? I've tried both - but without success because I'm sure my code is incomplete or I get an error... could you possibly write a complete example of your event procedure?
I've gotten myself so confused by this I just can't think straight anymore! I currently am trying-and I know it's probably really the wrong or hard way to do this:

button - on click
docmd.runmacro "gotosubform" (which is a data validation macro - condition = "" then msgbox - need data)
docmd.runmacro "open inspection signoff form"
exit sub

On the button click, it runs the docmd.runmacro "gotosubform", then it goess to the subform, validates the data - tells me data is missing and then closes (because the next line of code is docmd.runmacro "open inspection signoff"
I've tried cancelevent - I'm totally missing this..and I'm not a programmer, just can't figure out to change that title under my moniker. Appreciate any help! Hope what I've written makes sense. I'm sure this is kid's stuff for many of the "gurus" out there!

Thanks!

 

[ItIsHardToProgram]

At first I thought you wanted to cancel the closing of an uncomplete form???? yes???? if so..... Why are you doing it on a button....

If what you would want is to run the macro GoToSubForm and check for ur data validation, when your macro says "need data" store a boolean variable and test it with a if....
Private sub Validate_click()
docmd.runmacro "gotosubform"
if FormOk = true then
docmd.runmacro "open inspection signoff form"
Else
End if
End sub

If it is when user is trying to close the form, then do so like this.

its in the unload procedure, sry i wasnt really thinking.

Private Sub Form_unload(cancel As Integer)
If FormOk = True Then
MsgBox "There is a required field not filled."
Else
cancel = True
End If
End Sub

hope this is clear for you

 

[Myvee]

Hi - I appreciate your help - maybe I could explain a little more because the code doesn't work...

the main form has no close button - it forces the user to complete the transaction they have started and enter the information - that is why I need to close the form with a button - to finish their transaction and get out of the form they have to push the button.

The main form (Inspection Criteria and Collection)contains the "Inspection instructions" - they just view this so they know what data to enter.
The subform (called datacollection subform)is where they enter the data.
So we know that in order to close the form, they have to click the button. What I also want to do is be able to ensure that all fields in the subform have been filled with data - so I want the button that closes the form to do this step as well and, if the data is missing, stop the close process. So - I've tried code, I've tried macros - I've tried validation rule in tables - I am close but just missing something. I can get the button to validate data if I use a macro but then I can't get it to stop closing the form - I don't really care if i use a macro or code to do the validation - I just want it to work any way I can! LOL I hope I have explained myself more clearly.
Thanks!

 

[ItIsHardToProgram]

I think I might get what you mean.... you are closing the form with a docmd.close procedure? if so, place it in a if with a boolean that checks for all the txtbox filled,would that not do it? Else msgbox "you need to fill all txtboxes

 

[Myvee]

Yes! - that sounds exactly right - but I'm sorry - I'm not a programmer, just someone with a little bit of db knowledge and a lot of trial and error experience! so...and I'm really sorry to bother you...could you possibly give me an example of that code? I understand most of what you mean, but I don't understand all the string commands and code language and syntax enough yet. I'll give you a star just for being so helpful.

I don't want you to think I'm lazy either so I'll try and construct what it is I think you are getting at although you'll laugh at the following butcher job:

Private Subform btnopeninspectionsignoff on click()
If me.datacollection_subform = "" Then
Msgbox "Inspection Data is Missing" maybe a vb,YesNo,"Information Required" here??
Else
docmd.runmacro "open inspection signoff form"
end if
End sub

I know - this is totally not going to work - and that's why I'm here...
I know there is much more to it than this basic code I've just done - I usually can sort out my problems, but this one - and I'm sure it is really simple - I just can't get it! Needless to say, I'm feeling really silly, but I must know what I'm doing wrong - it's driving me crazy.

Thanks!

 

[ItIsHardToProgram]

The idea I am getting at is not really different then the one you were at in the first place.....

lets say you have 6 txtboxes in your subform named

FirstTxt, secondTxt, thirdTxt etc..... and your subform is named datacollection_subform.

On the onclick of your button it would go like this...

[blue]private sub command00_click()
If Forms![Datacollection_subform]![firstTxt] = "" Or  Forms![Datacollection_subform]![secondTxt] = "" Or
Forms![Datacollection_subform]![thirdTxt] = "" Then
[/blue][COLOR=green]'Take note that you need to refer every txtbox in your statement[/color][blue]
   Msgbox "There is one empty field in your subform"
Else
   docmd.runmacro "open inspection signoff form"
end if
end sub[/blue]

Then again, take note that the code will only test for empty controls in a subform... it will not perform anything else... if you want more, you would need to specify it clearly and I could maybe help you.

 

[ItIsHardToProgram]

an interesting add would be an inputbox for the empty field.

 

[Myvee]

Thanks ItIsHardToProgram - you are like a knight in shining armour here!
I'm going to try out that code now and see if it will do the trick.
I REALLY like your idea of adding an input box for the empty field - but I'm not even going to ask how to do that yet! I must master this field data validation issue first!

Thanks again - I'll be sure to let you know if it works.

 

[ItIsHardToProgram]

It is not very complicated.

I think I should let you write it down.

It goes in the same logic then the other code.

if theres a null txtbox in the subform then
x = inputbox("the msg you want")
'u can add title etc.... to the input box
txtbox = x
end if

its as simple as that.

 

[Myvee]

Oh I wish I could say that things worked out - and it is something really really stupid.... the error returned is "Inspection Manager V2 (that's the name of my db) cannot find the form "data collection subform" referred to in a macro expression or a visual basic code.

I've checked and rechecked my spelling, spacing - everything I can think of - is there any other possible reason this wouldn't recognize my subform? I typed in the code exactly as above with my controls referenced of course. Is it not recognizing this subform because it can't set focus to it? should i include a gotocontrol command or something?

I've also tried changing the code to Form![Data Collection _subform]!etc rather than forms!. I have tried brackets, quotation marks - she just won't work.
Do I also need to reference the main form? I'm almost ready to cry... boo hoo...

 

[PHV]

Have a look here:

http://support.microsoft.com/?kbid=209099

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ItIsHardToProgram]

Go to design view, right click on the BORDER of your subform and select properties, look at the name in the properties and copy paste it in your code.

After that, every time you see
Forms![highlight][Datacollection_subform][/highlight]![firstTxt] = ""

replace it by [subform name here]


everything should work after...

 

[Myvee]

Hello!

Well thank you PHV - that reference took care of my syntax problem and thank you ItIsHardToProgram for the tip - I did look at that too - nothing wrong there.. BUT...
I'm back at square one! I no longer get my error that it doesn't recognize my form thanks to the proper syntax, but when the button is clicked it seems to just jump right over the data validation code (the message box "Data is missing etc.. does not popup) and goes right to the docmd.runmacro "open inspection sign-off form".
I tried putting a docmd.cancelevent or a CancelEvent in there - doesn't work. Maybe I've put it in the wrong spot
Perhaps I should try IsNull - but I don't think I should do that. Any suggestions? Thanks to all for the help thus far!

 

[Myvee]

You are going to be so proud of your newbie dbperson here! I got it! Can you believe it? I tried IsNull and it works perfectly (fingers are crossed). I feel like I just won a prize LOL.

Thank you again for all your help!

Here is a summary of the code just so you can see

Private Sub btnAssignDisposition_Click()
If IsNull(Forms![Inspection Criteria and Collection]![DataCollectionSubform].Form![txtC1P1]) Or IsNull(Forms![Inspection Criteria and Collection]![DataCollectionSubform]![txtC2P2]) Then
MsgBox "I hope this works", vbCritical, "Inspection Data Required"
Else
DoCmd.RunMacro "open inspection sign-off form"
End If
End Sub

 

[PHV]

To test for Null, Space or ZeroLengthString:
Private Sub btnAssignDisposition_Click()
With Forms![Inspection Criteria and Collection]![DataCollectionSubform].Form
If Trim(![txtC1P1] & "") = "" Or Trim(![txtC2P2] & "") = "" Then
MsgBox "I hope this still works", vbCritical, "Inspection Data Required"
Else
DoCmd.RunMacro "open inspection sign-off form"
End If
End With
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886