Form Design use VBA - Access Developer Forum


	Back to Access Developer Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Form Design use VBA

Robert Stott

3 years ago

I am looking for advice on how to approach a problem. I have two forms that generally operate the same with the VBA code. One form works perfectly. The has one has a problem and try fix it another problems show up. Each Database uses a different set of data.
Here what's happening: there are 2 (Pick & Open) Cmdbtns and (one check box and one text object that are fields in the table).
The idea was to have the code (Sub-Routine) sees if the text field is null and makes PickCmdbtn active and red while the other OpenCmdbtn is inactive and blue.(PickCmdbtn, when clicked opens a dialog box and allow me to capture the path to a folder and store it the text field)
I fixed the problem by placing code (to recheck the text box status) in forms On Current event that s, that fixed the problem but created a new problem, now when I go to a new record it doesn't like the isNull, that I used in the code, and automatically assigning a TableID and won't let me leave the record until I populate one of the fields. (If I don't populate the field I have go to design view and close the form.)
If the text field is not null then the checkbox is check and that PickCmdbtn is inactive & blue and the other OpenCmdbtn is active and red. (OpenCmdbtn when clicked opens a word/pdf file).
I have checked the code between the two forms and found only the code that used to fix the as different. I even retyped all the code that was related to the problem.
One other thing that I don't understand, if checkbox fields in the table,(they are all 'Yes') access doesn't know if they are check ( the box has the black dot) when I open the form. - If I check the box that fixes everything, but that not a fix.
This database is for personnel use only ' I use it to track (way to much knowledge to hold in my 80 year old brain) my notes on the Access Training I am taking from you guys. I can provide any information you may need, Code or Images. I mainly looking for direction.

I know too many words!

	Kevin Robertson @Reply 3 years ago
Set a default value for the checkbox of either Yes or No.

Robert Stott OP @Reply

3 years ago

Thanks! That solved the check box side of the problem but the new record problem has reoccurred - click on add a new record button and it auto assigned an ID(Key) therefore I either fill something in or go to form view - I wish I could see a code difference between the form, one works and one doesn't. I even replaced code from one form to other to see if that made a difference. Using MsgBox all over the code trying trace the progress. I know there is something set wrong to cause this problem.

	Kevin Robertson @Reply 3 years ago
Can you post your code. We will be unable to troubleshoot the problem without it. Some screenshots may also prove helpful.

Robert Stott OP @Reply

3 years ago

Option Compare Database
Option Explicit
'The is for Training Courses
Private Sub EnableDisableButtons()
If Me.PickFolderChk Then
    If IsNull(FolderLocation) Or FolderLocation = "" Then
        Me.FolderPathBtn.Enabled = True
        Me.FolderPathBtn.BackColor = RGB(180, 90, 90)
    Else
        Me.FolderPathBtn.Enabled = False
        Me.FolderPathBtn.BackColor = RGB(50, 100, 150)
        Me.OpenFolderBtn.Enabled = True
        Me.OpenFolderBtn.BackColor = RGB(180, 90, 90)
    End If
Else
    Me.FolderPathBtn.Enabled = False
    Me.FolderPathBtn.BackColor = RGB(50, 100, 150)
    Me.OpenFolderBtn.Enabled = False
    Me.OpenFolderBtn.BackColor = RGB(50, 100, 150)
    Me.FolderLocation = ""
End If
If Me.PickFileChk Then
    If IsNull(FileLocation) Or FileLocation = "" Then
        Me.FilePathBtn.Enabled = True
        Me.FilePathBtn.BackColor = RGB(180, 90, 90)
    Else
        Me.FilePathBtn.Enabled = False
        Me.FilePathBtn.BackColor = RGB(50, 100, 150)
        Me.OpenFileBtn.Enabled = True
        Me.OpenFileBtn.BackColor = RGB(180, 90, 90)
    End If
Else
    Me.FilePathBtn.Enabled = False
    Me.FilePathBtn.BackColor = RGB(50, 100, 150)
    Me.OpenFileBtn.Enabled = False
    Me.OpenFileBtn.BackColor = RGB(50, 100, 150)
    Me.FileLocation = ""
End If
    Me.Recordset.Requery
End Sub

Private Sub CmdFirst_Click()
    On Error Resume Next
    DoCmd.GoToRecord , , acFirst
End Sub

Private Sub CmdBack_Click()
    On Error Resume Next
    DoCmd.GoToRecord , , acPrevious
End Sub

Private Sub CmdLast_Click()
    On Error Resume Next
    DoCmd.GoToRecord , , acLast
End Sub
Private Sub CmdNext_Click()
    On Error Resume Next
    DoCmd.GoToRecord , , acNext
End Sub
Private Sub CmdNew_Click()
    On Error Resume Next
    DoCmd.GoToRecord , , acNewRec
End Sub
Private Sub btnCloseEditAudioRecord_Click()
    On Error Resume Next
    DoCmd.Close
End Sub

Private Sub btnDeleteRecord_Click()
    On Error Resume Next
    Dim rst As Recordset
    Dim strBookmark As String

    Set rst = Me.RecordsetClone
   rst.MoveNext
        If Not rst.EOF Then                 ' if not end-of-file
            strBookmark = rst.Bookmark      ' ...save the next record's bookmark
            rst.Bookmark = Me.Bookmark      ' ...go back to the record to delete
            rst.Delete                      ' ...delete the record
            Me.Requery
            Me.Bookmark = strBookmark       ' ...and return to the saved bookmark
        Else
            rst.Delete                      ' ...just delete the record
            Me.Requery
            rst.MoveLast                    ' ...move to the last record
            Me.Bookmark = rst.Bookmark      ' ...position the form to it
        End If
End Sub

Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)
    Me.Recordset.MoveLast
End Sub

Private Sub Form_Current()
On Error Resume Next

    EnableDisableButtons

'Enable and or Disable Record Navigation Buttons
If Me.CurrentRecord = 1 Then
    Me.CmdBack.Enabled = False
    Me.CmdFirst.Enabled = False
Else
    Me.CmdBack.Enabled = True
    Me.CmdFirst.Enabled = True
End If
If Me.CurrentRecord = Me.Recordset.RecordCount Then
    Me.CmdLast.Enabled = False
Else
    Me.CmdLast.Enabled = True
End If
If Me.CurrentRecord >= Me.Recordset.RecordCount Then
    Me.CmdNext.Enabled = False
Else
    Me.CmdNext.Enabled = True
End If
If Me.NewRecord Then
    Me.CmdNew.Enabled = False
Else
    Me.CmdNew.Enabled = True
End If

'Record Count Label
    If Me.NewRecord Then
        Me.lblRecordCounter.Caption = "New Record"
    Else
        Me.lblRecordCounter.Caption = _
        "Record " & Me.CurrentRecord & " Of " & Me.Recordset.RecordCount
    End If
'    MsgBox "On Current"
End Sub

Private Sub PickFileChk_Click()
    On Error Resume Next
'    MsgBox "PickFileChk"
'    EnableDisableButtons
End Sub

'Open Dialog Window to choose and save a files path.
Private Sub FilePathBtn_Click()
    On Error Resume Next
    With Application.FileDialog(msoFileDialogFilePicker)
        .Show
        Me!LocationFilePath = .SelectedItems(1)
    End With
    MsgBox "FilePathBtn"
    EnableDisableButtons
End Sub
'Open Dialog Window to choose and save a folders bath
Private Sub OpenFileBtn_Click()
    On Error Resume Next
    Application.FollowHyperlink Me.LocationFilePath
    MsgBox "OpenFileBtn"
    EnableDisableButtons
End Sub
'Open Dialong and allows user to choice file location and return path to folder
Private Sub FolderPathBtn_Click()
    On Error Resume Next
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Show
        Me!FolderLocation = .SelectedItems(1)
    End With
'   MsgBox "FolderBtn"
'    EnableDisableButtons
End Sub

'Open Dialog Window Folder
Private Sub OpenFolderBtn_Click()
    On Error Resume Next
    Application.FollowHyperlink Me.FolderLocation
'    MsgBox "OpenFolderBtn"
'    EnableDisableButtons

End Sub

Private Sub PickFolderChk_Click()
    On Error Resume Next
'    MsgBox "PickFolderChk"
'    EnableDisableButtons
End Sub

	Kevin Robertson @Reply 3 years ago
In your On Current event: `If Not Me.NewRecord Then EnableDisableButtons` OR `If Not IsNull(YourID) Then EnableDisableButtons` This will prevent the EnableDisableButtons code from running when you go to a new record.

	Robert Stott OP @Reply 2 years ago
Thanks so much Kevin! I have everything working as planned. Can you suggest courses, Tech Help or other resources about the IF Not concept. This really start the New Year off right!!!

	Kevin Robertson @Reply 2 years ago
And Or Not

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Developer Forum.

Next Unseen

New Feature: Comment Live View