Need help with Code

D

Dave31

Registered User.
Local time
Today, 19:25
Joined
Oct 11, 2006
Messages
68
Hi

Sorry to be vague in the title, im not completely sure what it falls under.
I have an issue that in theory, should work (I think) but its not. I have been sitting here for the past few hours pulling out my hair thinking hard to why it’s not working. My access isn’t that great so im sure the issue is somewhere in the coding, I just can’t seem to spot it.
Here’s the code:

Code: 
Private Sub Command45_Click()
On Error GoTo Err_Command45_Click
Dim response, response2, response3, response4, response5, response6, response7, response8
Dim finish1, finish2, finish3, finish4, finish5, finish6, finish7
    
    If IsNull(programName) Then
    response = MsgBox("Program Name not filled in", vbExclamation + vbOKOnly)
    Else
    finish1 = "1"
    End If
    
    If IsNull(productName) Then
    response2 = MsgBox("Product Name not filled in", vbExclamation + vbOKOnly)
    Else
    finish2 = "1"
    End If
    
    If IsNull(phaseName) Then
    response3 = MsgBox("Phase Name not filled in", vbExclamation + vbOKOnly)
    Else
    finish3 = "1"
    End If
    
    If IsNull(workProduct) Then
    response4 = MsgBox("Work Product not filled in ", vbExclamation + vbOKOnly)
    Else
    finish4 = "1"
    End If
    
    If IsNull(scheduledReviewDate) Then
    response5 = MsgBox("Planned Review Date not filled in", vbExclamation + vbOKOnly)
    Else
    finish5 = "1"
    End If
    
    If Me.participantSUBForm.Form.flag1 = "1" Then
        If IsNull(participantSUBForm.Form.CDSID) Then
            response7 = MsgBox("Author not filled in", vbExclamation + vbOKOnly)
        Else
            finish6 = "1"
        End If
    participantSUBForm.SetFocus
    DoCmd.GoToRecord , , acNext
    End If
        
    If Me.participantSUBForm.Form.flag1 = "2" Then
        If IsNull(participantSUBForm.Form.CDSID) Then
            response8 = MsgBox("Leader not filled in", vbExclamation + vbOKOnly)
        Else
            finish7 = "1"
        End If
    End If

If finish1 = "1" And finish2 = "1" And finish3 = "1" And finish4 = "1" And finish5 = "1" And finish6 = "1" And finish7 = "1" Then
    
    response6 = MsgBox("Save Work Product?", vbInformation + vbOKCancel)
    If response6 = vbOK Then
        workProductID2 = workProductID
        meetingStatus = "Pending"
        peerReviewStatus = "Open"
        DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
        DoCmd.Close
    Else
        Me.Undo
    End If
    Cancel = True
End If
Exit_Command45_Click:
    Exit Sub

Err_Command45_Click:
    MsgBox Err.description
    Resume Exit_Command45_Click
    
End Sub

I know its fairly long, put as you can see, its fairly straight forward.
Its checking to see if all the fields are filled in, it also checks two fields in a subform (the reason for the flag1's is because it only checks two records in the subform but there can be multiple records in there)
Now, the problem is, in theory, if all the fields have been filled in, all the finish1-7 will have a value "1". So at the end, if all the finish1-7 are “1”’s, it should ask if you want to save the record! but the problem is, it doesn’t! No msgbox appears when you click save (in theory, it should)
Any ideas and help will be greatly appreciated

Many Thanks :)
 
Dim finish1, finish2, finish3, finish4, finish5, finish6, finish7

You are not declaring your variables properly! ! ! It should be:
Dim finish1 as String, finish2 as String, ...etc...

This may or may not have something to with it, but at the very least it is good practice to properly define your variables.

Why are you storing all the responses when you are not doing anything with them? If you just want to put up a msgbox... do it like so:
MsgBox "Program Name not filled in", vbExclamation + vbOKOnly

As to putting the finger on the spot why its not working?? I have no clue... I think it should... but I am probably missing something...
 
Thanks for your help. Im a fairly lazy programmer, and i must have never fully taught myself declaring the variables correclty.

Anyway, the reason why i have those responses is because i need the code to know if the field is fill in or not! if its not, brings up an error message. if it is, then store (in a variable) that its fill in. So at the end, when (and only when) all fields are filled in, be able to save.

If you can think of a different way to carry out this task, ill be really gratefull.

:)
 
Your "Finish1"..."Finish2"... so on, seems like an extra step... If all you want to do is not to allow it to save if the field has no data put that in the code. Something like this.....

Private Sub Form_BeforeUpdate(Cancel As Integer)

If IsNull(programName) Or programName = "" Then
MsgBox "Program Name is a required Field", vbCritical + vbOKOnly + vbDefaultButton1, "Missing Data"
programName.SetFocus
Cancel = True
End If
 
The problem is, i still need an identifier to progress to the save option.
so if i have your code and then the save section in the button click, it will check all the fields, and if the fields are empty, it will display the warning, then go on to save (which i dont want). So i need something to say whenn all fields are filled then save, or just display the warnings if any fields are empty.
Understand where im coming from?
 
Have you tried it with this code? I think you will find a Save command button will NOT save the record with this code on your fields. The "Cancel = True" on the before update takes care of that.
 
I have tried it Curtis, but once i select the first field and select an item from the combo box, it then prompts me that i havnt filled in the other fields, even though i dont want the error checking carried out till the Save button is selected. Any Ideas how to sort this out?
 
No msgbox appears when you click save (in theory, it should)
Click to expand...
you are calling this from a button called Command45, is this your save button?

You need to check for empty strings as well and you dont need most of those variables!

Code: 
Private Sub Command45_Click()
On Error GoTo Err_Command45_Click
Dim response As Boolean
Dim response6

response = True
    
    If IsNull(programName) Or programName = "" Then
    MsgBox "Program Name not filled in", vbExclamation + vbOKOnly
    response = False
    End If
    
    If IsNull(productName) Or productName = "" Then
    MsgBox "product Name not filled in", vbExclamation + vbOKOnly
    response = False
    End If
    
    If IsNull(phaseName) Or phaseName = "" Then
    MsgBox "Phase Name not filled in", vbExclamation + vbOKOnly
    response = False
    End If
    
        If IsNull(workProduct) Or programName = "" Then
    MsgBox "Work Product not filled in", vbExclamation + vbOKOnly
    response = False
    End If

    If IsNull(scheduledReviewDate) Or scheduledReviewDate = "" Then
    MsgBox "Planned Review Date not filled in", vbExclamation + vbOKOnly
    response = False
    End If

    
    If Me.participantSUBForm.Form.flag1 = "1" Then
        If IsNull(participantSUBForm.Form.CDSID) Then
            MsgBox "Author not filled in", vbExclamation + vbOKOnly
            response = False
        End If
    participantSUBForm.SetFocus
    DoCmd.GoToRecord , , acNext
    End If
        
    If Me.participantSUBForm.Form.flag1 = "2" Then
        If IsNull(participantSUBForm.Form.CDSID) Then
            MsgBox "Leader not filled in", vbExclamation + vbOKOnly
            response = False
        End If
    End If

If response = True Then
    response6 = MsgBox("Save Work Product?", vbInformation + vbOKCancel)
    If response6 = vbOK Then
        workProductID2 = workProductID
        meetingStatus = "Pending"
        peerReviewStatus = "Open"
        DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
        DoCmd.Close
    Else
        Me.Undo
    End If
    Cancel = True
End If
Exit_Command45_Click:
    Exit Sub

Err_Command45_Click:
    MsgBox Err.Description
    Resume Exit_Command45_Click
    
End Sub

Peter
 
Fantastic, thanks Peter, that was exactly what i needed :D
 
namliam said:
but I am probably missing something...
Click to expand...
DUH! Empty strings... *Knocks me upside the head*

Also I didnt notice this, but Peter pointed to it... Give your buttons "proper" names, dont leave the default names like Command45 but rather use cmdSave or somesort. Makes maintenance much friendlier.
 
yeh, i normaly name my tables and queries fairly well, and i did mention i was a lazy programmer, so thats the reason i dont change my button names, i know i should but... :P
 
You must log in or register to reply here.

Similar threads

S
Solved Need help with number as text
2
Replies
23
Views
1,673
KitaYama
K
Mike Krailo
Solved Need help with before update code logic?
Replies
10
Views
1,152
Mike Krailo
Mike Krailo
C
Help with looping through records
Replies
11
Views
643
cricketbird
C
V
Need help with VBA to check for required fields
Replies
6
Views
1,028
MajP
MajP
S
sms send bottam vb code need help
Replies
12
Views
775
Gasman
Gasman

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom