Back to Basics

G

Guest

Dear All, I have datebase with a table (GenReg) and a form (ALLFORM) linked
to the table. Having spent the last three weeks going round in circles I
have decided to go back to basics and start coding the form from scratch.
What I would like to achieve is this.
1. Two unbound controls (doesn't matter they are) so that when selected will
enbable some fields and not others.
2. One unbound control (doesn't matter what it is) so then if the user
selects it will check for any empty fields that should have been entered
(dependant upon 1 above), if an error found return to the current form so
allow completion or cancel the current record/not save and return form to
netural. If all fields which should contain data do then I would like this
control to print report and send report as an attachment to email using
Outlook.

I do not know which way is the best to achieve even just enable some and
disable other fields and to get them to validate the data (the report & email
could come later). I would appreciate it anyone could point me in the right
direction as no matter which way I try it using my current method I can't
seem to get it to work.

CURRENT METHOD ... Command Button AddNewRecIn ON_CLICK Code
If Me.AddNewRecIN.Enabled = True Then
Me.AddNewRecOUT.Enabled = False
Me.DateSent.Visible = False
Me.SentTo.Visible = False

DoCmd.GoToRecord , , acNewRec

'Disable/Grey Out the "Edit Record Button", "Delete Recordbutton ", Edit
Save Button & Text and Deletion Confirm Button116
Me!EditRec.Enabled = False
Me!RegDelRec.Enabled = False
Me!EditRecSave.Enabled = False
Me!Label103.Visible = False
Me!Command116.Enabled = False

'Disable/Grey Out the Text Boxes Not Applicable To A New Record
Me!ReasonsforEdit.Enabled = False
Me!PersonReqDel.Enabled = False
Me!DeleteDate.Enabled = False
Me!DeptReqDel.Enabled = False
Me!ReasonforDel.Enabled = False
Me!RegNoIDNo.Enabled = False

If Me.NewRecord Then
Dim strWhere As String
Dim varResult As Variant

strWhere = "RegisterNumber Like """ & Format(Date, "yy") & "*"""
varResult = DMax("RegisterNumber", "GenReg", strWhere)

If IsNull(varResult) Then
Me.RegisterNumber = Format(Date, "yy") & "-000001"
Else
Me.RegisterNumber = Left(varResult, 3) & _
Format(Val(right(varResult, 4)) + 1, "000000")
End If
End If
End If

Exit_AddNewRecIN_Click:
Exit Sub

Err_AddNewRecIN_Click:
MsgBox Err.Description
Resume Exit_AddNewRecIN_Click

...........................................................AddNewRecOUT
On-Click Code

If Me.AddNewRecOUT.Enabled = True Then
Me.AddNewRecIN.Visible = False
Me.DateReceived.Visible = False
Me.ReceivedFrom.Visible = False

DoCmd.GoToRecord , , acNewRec

'Disable/Grey Out the "Edit Record Button", "Delete Recordbutton ", Edit
Save Button & Text and Deletion Confirm Button116
Me!EditRec.Enabled = False
Me!RegDelRec.Enabled = False
Me!EditRecSave.Enabled = False
Me!Label103.Visible = False
Me!Command116.Enabled = False

'Disable/Grey Out the Text Boxes Not Applicable To A New Record
Me!ReasonsforEdit.Enabled = False
Me!PersonReqDel.Enabled = False
Me!DeleteDate.Enabled = False
Me!DeptReqDel.Enabled = False
Me!ReasonforDel.Enabled = False
Me!RegNoIDNo.Enabled = False

If Me.NewRecord Then
Dim strWhere As String
Dim varResult As Variant

strWhere = "RegisterNumber Like """ & Format(Date, "yy") & "*"""
varResult = DMax("RegisterNumber", "GenReg", strWhere)

If IsNull(varResult) Then
Me.RegisterNumber = Format(Date, "yy") & "-000001"
Else
Me.RegisterNumber = Left(varResult, 3) & _
Format(Val(right(varResult, 4)) + 1, "000000")
End If
End If
End If

Exit_AddNewRecOUT_Click:
Exit Sub

Err_AddNewRecOUT_Click:
MsgBox Err.Description
Resume Exit_AddNewRecOUT_Click

End Sub

.............................................................SaveAddNewRec1
On_Click Code

Private Sub SaveAddNewRec1_Click()
On Error GoTo Err_SaveAddNewRec1_Click

If Me.Dirty Then
RunCommand acCmdSaveRecord
End If

'PRINT DOCUMENT CONTROL REPORT

' Dim strWhere As String
' Dim stDocName As String
'
' If Me.NewRecord Then 'Check there is a record to print
' Else
' strWhere = "[RegisterNumber] = """ & Me.[RegisterNumber] & """"
' DoCmd.OpenReport "RegEntryINFormRpt", acViewPreview, , strWhere
' End If
'
' If Not IsNull(Me.DateReceived) And Not IsNull(Me.ReceivedFrom) Then
' DoCmd.OpenReport "RegEntryINFormRpt", acViewPreview, , strWhere
' Else
' If Not IsNull(Me.DateSent) And Not IsNull(Me.SentTo) Then
' DoCmd.OpenReport "RegEntryOUTFormRpt", acViewPreview, , strWhere
' End If
' End If

'OPEN OUTLOOK AND SENT REPORT TO EMAIL AS ATTACHMENT READY FOR SENDING

' stDocName = "EMailRptEEOUT"
' stDocName = "EMailRptEE"
' strWhere = "[RegisterNumber] = """ & Me.[RegisterNumber] & """"
'
' If Not IsNull(Me.DateReceived) Then
' DoCmd.OpenReport "EMailRptEE", acViewPreview, , strWhere
' DoCmd.RunMacro "EMailRptM"
' Else
' If Not IsNull(Me.DateSent) Then
' DoCmd.OpenReport "EMailRptEE", acViewPreview, , strWhere
' DoCmd.RunMacro "EMailRptMEE"
' End If
' End If

Me.AddNewRecOUT.Enabled = True
Me.DateSent.Visible = True
Me.SentTo.Visible = True

Exit_SaveAddNewRec1_Click:
Exit Sub

Err_SaveAddNewRec1_Click:
MsgBox Err.Description
Resume Exit_SaveAddNewRec1_Click

End Sub

Private Sub AddNewRecIN_Click()
On Error GoTo Err_AddNewRecIN_Click

If Me.AddNewRecIN.Enabled = True Then
Me.AddNewRecOUT.Enabled = False
Me.DateSent.Visible = False
Me.SentTo.Visible = False

DoCmd.GoToRecord , , acNewRec

'Disable/Grey Out the "Edit Record Button", "Delete Recordbutton ", Edit
Save Button & Text and Deletion Confirm Button116
Me!EditRec.Enabled = False
Me!RegDelRec.Enabled = False
Me!EditRecSave.Enabled = False
Me!Label103.Visible = False
Me!Command116.Enabled = False

'Disable/Grey Out the Text Boxes Not Applicable To A New Record
Me!ReasonsforEdit.Enabled = False
Me!PersonReqDel.Enabled = False
Me!DeleteDate.Enabled = False
Me!DeptReqDel.Enabled = False
Me!ReasonforDel.Enabled = False
Me!RegNoIDNo.Enabled = False


If Me.NewRecord Then
Dim strWhere As String
Dim varResult As Variant

strWhere = "RegisterNumber Like """ & Format(Date, "yy") & "*"""
varResult = DMax("RegisterNumber", "GenReg", strWhere)

If IsNull(varResult) Then
Me.RegisterNumber = Format(Date, "yy") & "-000001"
Else
Me.RegisterNumber = Left(varResult, 3) & _
Format(Val(right(varResult, 4)) + 1, "000000")
End If
End If
End If

Exit_AddNewRecIN_Click:
Exit Sub

Err_AddNewRecIN_Click:
MsgBox Err.Description
Resume Exit_AddNewRecIN_Click

End Sub

This always gives me a message of (if no fields entered) - first all the
msgbox text followed by "The Command or action 'l' isn't available now". Or
if I've entered at least one field with data - first all the msbgox text
followed by "The Run Command action was canceled". I greyed out the print &
email command until the first part (data Validation) works.

Could someone please tell me what I am doing wrong, from a very despondent
newbie. Thank you.
 
G

Guest

Dear All, After posting this question I completly evaluated what I was
trying to achieve and approach it from a different angle, I managed to get
the command button to disable until all required fields were met. A very big
thank you to all those who have helped me develop this database. I'm in the
process of 'tidying up' and still have a couple of questions which I will
post separately. Once again a very BIG BIG thank you from a very grateful
user of this forum. - Sue

Sue Wilkes said:
Dear All, I have datebase with a table (GenReg) and a form (ALLFORM) linked
to the table. Having spent the last three weeks going round in circles I
have decided to go back to basics and start coding the form from scratch.
What I would like to achieve is this.
1. Two unbound controls (doesn't matter they are) so that when selected will
enbable some fields and not others.
2. One unbound control (doesn't matter what it is) so then if the user
selects it will check for any empty fields that should have been entered
(dependant upon 1 above), if an error found return to the current form so
allow completion or cancel the current record/not save and return form to
netural. If all fields which should contain data do then I would like this
control to print report and send report as an attachment to email using
Outlook.

I do not know which way is the best to achieve even just enable some and
disable other fields and to get them to validate the data (the report & email
could come later). I would appreciate it anyone could point me in the right
direction as no matter which way I try it using my current method I can't
seem to get it to work.

CURRENT METHOD ... Command Button AddNewRecIn ON_CLICK Code
If Me.AddNewRecIN.Enabled = True Then
Me.AddNewRecOUT.Enabled = False
Me.DateSent.Visible = False
Me.SentTo.Visible = False

DoCmd.GoToRecord , , acNewRec

'Disable/Grey Out the "Edit Record Button", "Delete Recordbutton ", Edit
Save Button & Text and Deletion Confirm Button116
Me!EditRec.Enabled = False
Me!RegDelRec.Enabled = False
Me!EditRecSave.Enabled = False
Me!Label103.Visible = False
Me!Command116.Enabled = False

'Disable/Grey Out the Text Boxes Not Applicable To A New Record
Me!ReasonsforEdit.Enabled = False
Me!PersonReqDel.Enabled = False
Me!DeleteDate.Enabled = False
Me!DeptReqDel.Enabled = False
Me!ReasonforDel.Enabled = False
Me!RegNoIDNo.Enabled = False

If Me.NewRecord Then
Dim strWhere As String
Dim varResult As Variant

strWhere = "RegisterNumber Like """ & Format(Date, "yy") & "*"""
varResult = DMax("RegisterNumber", "GenReg", strWhere)

If IsNull(varResult) Then
Me.RegisterNumber = Format(Date, "yy") & "-000001"
Else
Me.RegisterNumber = Left(varResult, 3) & _
Format(Val(right(varResult, 4)) + 1, "000000")
End If
End If
End If

Exit_AddNewRecIN_Click:
Exit Sub

Err_AddNewRecIN_Click:
MsgBox Err.Description
Resume Exit_AddNewRecIN_Click

..........................................................AddNewRecOUT
On-Click Code

If Me.AddNewRecOUT.Enabled = True Then
Me.AddNewRecIN.Visible = False
Me.DateReceived.Visible = False
Me.ReceivedFrom.Visible = False

DoCmd.GoToRecord , , acNewRec

'Disable/Grey Out the "Edit Record Button", "Delete Recordbutton ", Edit
Save Button & Text and Deletion Confirm Button116
Me!EditRec.Enabled = False
Me!RegDelRec.Enabled = False
Me!EditRecSave.Enabled = False
Me!Label103.Visible = False
Me!Command116.Enabled = False

'Disable/Grey Out the Text Boxes Not Applicable To A New Record
Me!ReasonsforEdit.Enabled = False
Me!PersonReqDel.Enabled = False
Me!DeleteDate.Enabled = False
Me!DeptReqDel.Enabled = False
Me!ReasonforDel.Enabled = False
Me!RegNoIDNo.Enabled = False

If Me.NewRecord Then
Dim strWhere As String
Dim varResult As Variant

strWhere = "RegisterNumber Like """ & Format(Date, "yy") & "*"""
varResult = DMax("RegisterNumber", "GenReg", strWhere)

If IsNull(varResult) Then
Me.RegisterNumber = Format(Date, "yy") & "-000001"
Else
Me.RegisterNumber = Left(varResult, 3) & _
Format(Val(right(varResult, 4)) + 1, "000000")
End If
End If
End If

Exit_AddNewRecOUT_Click:
Exit Sub

Err_AddNewRecOUT_Click:
MsgBox Err.Description
Resume Exit_AddNewRecOUT_Click

End Sub

............................................................SaveAddNewRec1
On_Click Code

Private Sub SaveAddNewRec1_Click()
On Error GoTo Err_SaveAddNewRec1_Click

If Me.Dirty Then
RunCommand acCmdSaveRecord
End If

'PRINT DOCUMENT CONTROL REPORT

' Dim strWhere As String
' Dim stDocName As String
'
' If Me.NewRecord Then 'Check there is a record to print
' Else
' strWhere = "[RegisterNumber] = """ & Me.[RegisterNumber] & """"
' DoCmd.OpenReport "RegEntryINFormRpt", acViewPreview, , strWhere
' End If
'
' If Not IsNull(Me.DateReceived) And Not IsNull(Me.ReceivedFrom) Then
' DoCmd.OpenReport "RegEntryINFormRpt", acViewPreview, , strWhere
' Else
' If Not IsNull(Me.DateSent) And Not IsNull(Me.SentTo) Then
' DoCmd.OpenReport "RegEntryOUTFormRpt", acViewPreview, , strWhere
' End If
' End If

'OPEN OUTLOOK AND SENT REPORT TO EMAIL AS ATTACHMENT READY FOR SENDING

' stDocName = "EMailRptEEOUT"
' stDocName = "EMailRptEE"
' strWhere = "[RegisterNumber] = """ & Me.[RegisterNumber] & """"
'
' If Not IsNull(Me.DateReceived) Then
' DoCmd.OpenReport "EMailRptEE", acViewPreview, , strWhere
' DoCmd.RunMacro "EMailRptM"
' Else
' If Not IsNull(Me.DateSent) Then
' DoCmd.OpenReport "EMailRptEE", acViewPreview, , strWhere
' DoCmd.RunMacro "EMailRptMEE"
' End If
' End If

Me.AddNewRecOUT.Enabled = True
Me.DateSent.Visible = True
Me.SentTo.Visible = True

Exit_SaveAddNewRec1_Click:
Exit Sub

Err_SaveAddNewRec1_Click:
MsgBox Err.Description
Resume Exit_SaveAddNewRec1_Click

End Sub

Private Sub AddNewRecIN_Click()
On Error GoTo Err_AddNewRecIN_Click

If Me.AddNewRecIN.Enabled = True Then
Me.AddNewRecOUT.Enabled = False
Me.DateSent.Visible = False
Me.SentTo.Visible = False

DoCmd.GoToRecord , , acNewRec

'Disable/Grey Out the "Edit Record Button", "Delete Recordbutton ", Edit
Save Button & Text and Deletion Confirm Button116
Me!EditRec.Enabled = False
Me!RegDelRec.Enabled = False
Me!EditRecSave.Enabled = False
Me!Label103.Visible = False
Me!Command116.Enabled = False

'Disable/Grey Out the Text Boxes Not Applicable To A New Record
Me!ReasonsforEdit.Enabled = False
Me!PersonReqDel.Enabled = False
Me!DeleteDate.Enabled = False
Me!DeptReqDel.Enabled = False
Me!ReasonforDel.Enabled = False
Me!RegNoIDNo.Enabled = False


If Me.NewRecord Then
Dim strWhere As String
Dim varResult As Variant

strWhere = "RegisterNumber Like """ & Format(Date, "yy") & "*"""
varResult = DMax("RegisterNumber", "GenReg", strWhere)

If IsNull(varResult) Then
Me.RegisterNumber = Format(Date, "yy") & "-000001"
Else
Me.RegisterNumber = Left(varResult, 3) & _
Format(Val(right(varResult, 4)) + 1, "000000")
End If
End If
End If

Exit_AddNewRecIN_Click:
Exit Sub

Err_AddNewRecIN_Click:
MsgBox Err.Description
Resume Exit_AddNewRecIN_Click

End Sub

This always gives me a message of (if no fields entered) - first all the
msgbox text followed by "The Command or action 'l' isn't available now". Or
if I've entered at least one field with data - first all the msbgox text
followed by "The Run Command action was canceled". I greyed out the print &
email command until the first part (data Validation) works.

Could someone please tell me what I am doing wrong, from a very despondent
newbie. Thank you.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Run Time Error 3314 1
Main Form, Sub-Form 3
Subform Search Function 2
Data Validation from Delete Command Button 1
Modify Message Box Cancel Button 6
Using a continuous form's filter to generate a report. 1
Scheduling Query/Clashing Events 4
HOW TO Simplify following code 2

Top