Database Problems (1 Viewer)

rayape

Registered User.
Local time
Yesterday, 23:22
Joined
Dec 12, 2012
Messages
56
Hello All, please refer to the attached database. I have the following questions. I would appreciate your guidance. Thank you.

1. Are the tables/fields/relationships/queries set up correctly?

2. In frm_Products, the pull down menu to filter the list works only sometimes. I don't know why! Would you please help me understand why?

2. In frm_Submittal:
A) Command Button 'New Submittal' is intended to a new blank record. It also requeries the List Box for Available Products, so the list is complete (using vba code).
B) The Next Record and Previous Record Buttons (with arrows) are used to navigate records (using vba code).
C) The pull down menu 'Plant' filters the ListBox based on the Plant selection.
D) The Select Product button simply copies the selected Products to the Submitted Products box.

Used together, can controls mentioned in A, B, or C above mess up my database to stop functioning? Because, sometimes when I try to create a new record and use the next/previous buttons to go back and forth, the database suddenly stops functioning normally. For ex: the pull down menu 'Plant' which is supposed to filter the Available Products list stops working. When I select a Plant, the ListBox goes empty. I am not sure why these problems are happening.

Any diagnosis with remedies will be greatly appreciated.

Thanks again.

I don't know how to code. All the code I have is borrowed from other places and tweaked to "work".
 

Attachments

  • Database.accdb
    1.1 MB · Views: 132

June7

AWF VIP
Local time
Today, 01:22
Joined
Mar 9, 2014
Messages
5,423
Suggest you give controls more informative names than the defaults assigned by Access.

Make sure new record is committed. One way:

If Me.Dirty Then Me.Dirty = False

For navigation buttons, I use a sub that is called by buttons:

Code:
Public Sub ViewData(strDirection)
'called by forms used to view and navigate records

If Not IsNull(Form_SampleInfo.tbxViewDataFormNAME) Then
    DoCmd.Close acForm, Form_SampleInfo.tbxViewDataFormNAME, acSaveNo
End If

With Form_SampleInfo

.RecordsetClone.Bookmark = .Bookmark
Select Case strDirection
    Case "Quit"
        DoCmd.Close acForm, "SampleInfo", acSaveNo
    Case "Next"
        .RecordsetClone.MoveNext
        If Not .RecordsetClone.EOF Then
            DoCmd.GoToRecord acForm, "SampleInfo", acNext
        Else
            .RecordsetClone.MoveLast
            MsgBox "Last record."
            .btnNext.Enabled = False
        End If
        .btnPrevious.Enabled = True
    Case "Previous"
        .RecordsetClone.MovePrevious
        If Not .RecordsetClone.BOF Then
            DoCmd.GoToRecord acForm, "SampleInfo", acPrevious
        Else
            .RecordsetClone.MoveFirst
            MsgBox "First record."
            .btnPrevious.Enabled = False
        End If
        .btnNext.Enabled = True
End Select

End With

End Sub
 

rayape

Registered User.
Local time
Yesterday, 23:22
Joined
Dec 12, 2012
Messages
56
Suggest you give controls more informative names than the defaults assigned by Access.

Make sure new record is committed. One way:

If Me.Dirty Then Me.Dirty = False

For navigation buttons, I use a sub that is called by buttons:

Code:
Public Sub ViewData(strDirection)
'called by forms used to view and navigate records

If Not IsNull(Form_SampleInfo.tbxViewDataFormNAME) Then
    DoCmd.Close acForm, Form_SampleInfo.tbxViewDataFormNAME, acSaveNo
End If

With Form_SampleInfo

.RecordsetClone.Bookmark = .Bookmark
Select Case strDirection
    Case "Quit"
        DoCmd.Close acForm, "SampleInfo", acSaveNo
    Case "Next"
        .RecordsetClone.MoveNext
        If Not .RecordsetClone.EOF Then
            DoCmd.GoToRecord acForm, "SampleInfo", acNext
        Else
            .RecordsetClone.MoveLast
            MsgBox "Last record."
            .btnNext.Enabled = False
        End If
        .btnPrevious.Enabled = True
    Case "Previous"
        .RecordsetClone.MovePrevious
        If Not .RecordsetClone.BOF Then
            DoCmd.GoToRecord acForm, "SampleInfo", acPrevious
        Else
            .RecordsetClone.MoveFirst
            MsgBox "First record."
            .btnPrevious.Enabled = False
        End If
        .btnNext.Enabled = True
End Select

End With

End Sub


@June7 Thanks for the help. I will use more definitive names. Where do I paste this code? Other than using definitive names, what are other problems with my database/vba? Thank you all.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 09:22
Joined
Feb 19, 2013
Messages
16,553
other comments.

  • Don't use lookups in tables. see this link http://access.mvps.org/access/lookupfields.htm
  • Make your autonumbers primary keys.
  • Productcode is populated numeric, but stored as text
  • Index fields which you join/sort or filter on (you have no indexing as far as I can see). see this link https://www.access-programmers.co.u...ant-for-good-performance.291268/#post-1516326
  • Using captions for field names is OK but can lead to confusion so make sure you remember 6 months down the line why you have done it - you have a wrong 'plant' value and cannot find the 'plant' field because it is actually called plantLocation. Even more confusing for someone else.
 

rayape

Registered User.
Local time
Yesterday, 23:22
Joined
Dec 12, 2012
Messages
56
other comments.

  • Don't use lookups in tables. see this link http://access.mvps.org/access/lookupfields.htm
  • Make your autonumbers primary keys.
  • Productcode is populated numeric, but stored as text
  • Index fields which you join/sort or filter on (you have no indexing as far as I can see). see this link https://www.access-programmers.co.u...ant-for-good-performance.291268/#post-1516326
  • Using captions for field names is OK but can lead to confusion so make sure you remember 6 months down the line why you have done it - you have a wrong 'plant' value and cannot find the 'plant' field because it is actually called plantLocation. Even more confusing for someone else.

Thank you sir. Will fix them all.

Please let me know where to use the code.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 09:22
Joined
Feb 19, 2013
Messages
16,553
that is for June to answer but looks like you would put it in the form module. Then you would have buttons for each option - quit, next etc

for the quit button click event you would put

viewdata "Quit"

the next button

viewdata "next"

etc
 

rayape

Registered User.
Local time
Yesterday, 23:22
Joined
Dec 12, 2012
Messages
56
Thank you, @CJ_London

I created a module and pasted the code. Created a button on frm_Submittal and typed Viewdata "next". When I click the button, it shows runtime error 424. Object required.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 09:22
Joined
Feb 19, 2013
Messages
16,553
sorry, can't be much more help - needs June's input.

If you get an error, a line is highlighted in yellow which is the line the error occurred. What does it say?
 

June7

AWF VIP
Local time
Today, 01:22
Joined
Mar 9, 2014
Messages
5,423
Did you modify code to use your form names? I doubt you need the If Then block - I probably should not have included it.

I actually have the procedure in a general module because it is called by multiple forms that are interacting with Form_SampleInfo. But don't need to get into that. You only need it for one form. Don't even need to keep that Sub as is. Could take each button part and put it in individual button click events.
 

Gasman

Enthusiastic Amateur
Local time
Today, 09:22
Joined
Sep 21, 2011
Messages
14,037
@June7
I was looking at that code, but could not see how it would work for all forms?, so you could have one routine that would handle any form navigation?
 

June7

AWF VIP
Local time
Today, 01:22
Joined
Mar 9, 2014
Messages
5,423
I didn't say procedure works for all forms. I said it is called by multiple forms that are interacting with form SampleInfo. SampleInfo is the only form this procedure acts on.

Modify code to have an argument that passes form name or form object.
 

Gasman

Enthusiastic Amateur
Local time
Today, 09:22
Joined
Sep 21, 2011
Messages
14,037
I didn't say procedure works for all forms. I said it is called by multiple forms that are interacting with form SampleInfo. SampleInfo is the only form this procedure acts on.

Modify code to have an argument that passes form name or form object.
Ah OK, I misunderstood. :(
I thought it was meant to be a generic routine for form navigation.
 

rayape

Registered User.
Local time
Yesterday, 23:22
Joined
Dec 12, 2012
Messages
56
Did you modify code to use your form names? I doubt you need the If Then block - I probably should not have included it.

I actually have the procedure in a general module because it is called by multiple forms that are interacting with Form_SampleInfo. But don't need to get into that. You only need it for one form. Don't even need to keep that Sub as is. Could take each button part and put it in individual button click events.

Did you modify code to use your form names? I doubt you need the If Then block - I probably should not have included it.
I did modify the code to match my form names. It didn't work.

I actually have the procedure in a general module because it is called by multiple forms that are interacting with Form_SampleInfo. But don't need to get into that. You only need it for one form. Don't even need to keep that Sub as is. Could take each button part and put it in individual button click events.
I am not sure how to do this. Please help.

Thank you all.
 

June7

AWF VIP
Local time
Today, 01:22
Joined
Mar 9, 2014
Messages
5,423
If you want to provide db for analysis, follow instructions at bottom of my post.
 

rayape

Registered User.
Local time
Yesterday, 23:22
Joined
Dec 12, 2012
Messages
56
@June7 Please see the attached db. The button with caption New Command Button is where I inserted part of the code. Thank you for helping.
 

Attachments

  • Database.accdb
    1.1 MB · Views: 122

June7

AWF VIP
Local time
Today, 01:22
Joined
Mar 9, 2014
Messages
5,423
If you don't have a value passed for Select Case then don't use Select Case. Need Form_ prefix. Note this prefix is attached by VBA editor in the Project Objects list. This is one way to refer to form objects in VBA. Report would use Report_ prefix. Or since code is behind form, use Me.

You have Command10 and Command195 for btnNext and btnPrevious. As I said, use more informative names. Why a new button? Fix the existing buttons.
Code:
Private Sub Command10_Click()
With Me
.RecordsetClone.Bookmark = .Bookmark
.RecordsetClone.MovePrevious
If Not .RecordsetClone.BOF Then
    DoCmd.GoToRecord acForm, .Name, acPrevious
Else
    .RecordsetClone.MoveFirst
    MsgBox "First record."
    .Command10.Enabled = False
End If
.Command195.Enabled = True
End With
End Sub

Private Sub Command195_Click()
With Me
.RecordsetClone.Bookmark = .Bookmark
.RecordsetClone.MoveNext
If Not .RecordsetClone.EOF Then
    DoCmd.GoToRecord acForm, .Name, acNext
Else
    .RecordsetClone.MoveLast
    MsgBox "Last record."
    .Command195.Enabled = False
End If
.Command10.Enabled = True
End With
End Sub
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 09:22
Joined
Jul 9, 2003
Messages
16,243
I've adapted June7's excellent code in to a generic version which should work with any Form. Just add a module to your database, the command buttons to your form and you have a practically no code technique for adding the Next/Previous/Quit buttons to any form in any database.

See the video for more information:-

Next Previous Quit - Nifty Access


More information on my Nifty Access website here:-

 
Last edited:

rayape

Registered User.
Local time
Yesterday, 23:22
Joined
Dec 12, 2012
Messages
56
@June7 and/or others.

Please see the attached db. When I click the 'New Submittal' button, the form creates a new record and requeries the list box. At this point, if I use the 'Previous Record' button to see what my last entry was, an error message pops. It says 'No Current Record'.
What is causing this? Please help.
Thank you.
 

Attachments

  • Database.accdb
    1.2 MB · Views: 115

June7

AWF VIP
Local time
Today, 01:22
Joined
Mar 9, 2014
Messages
5,423
I designed this code for a form to view existing records, not to create new records. So never tested with that in mind. Sorry, didn't think of it with your earlier db version. Modify code. Can either manage availability of Next and Previous buttons in New Submittal and Save Record procedures or try this:
Code:
Private Sub btnNext_Click()
With Me
If Not .NewRecord Then
    .RecordsetClone.Bookmark = .Bookmark
    .RecordsetClone.MoveNext

    If Not .RecordsetClone.EOF Then
        DoCmd.GoToRecord acForm, .Name, acNext
    Else
        .RecordsetClone.MoveLast
        MsgBox "Last record."
        .btnNext.Enabled = False
    End If
    .btnPrevious.Enabled = True
End If
End With
End Sub

Private Sub btnPrevious_Click()
With Me
If Not .NewRecord Then
    .RecordsetClone.Bookmark = .Bookmark
    .RecordsetClone.MovePrevious
End If
If Not .RecordsetClone.BOF Then
    DoCmd.GoToRecord acForm, .Name, acPrevious
Else
    .RecordsetClone.MoveFirst
    MsgBox "First record."
    .btnPrevious.Enabled = False
End If
.btnNext.Enabled = True
End With
End Sub

Need to change the ControlTip text for btnNext.
 

rayape

Registered User.
Local time
Yesterday, 23:22
Joined
Dec 12, 2012
Messages
56
I designed this code for a form to view existing records, not to create new records. So never tested with that in mind. Sorry, didn't think of it with your earlier db version. Modify code. Can either manage availability of Next and Previous buttons in New Submittal and Save Record procedures or try this:
Code:
Private Sub btnNext_Click()
With Me
If Not .NewRecord Then
    .RecordsetClone.Bookmark = .Bookmark
    .RecordsetClone.MoveNext

    If Not .RecordsetClone.EOF Then
        DoCmd.GoToRecord acForm, .Name, acNext
    Else
        .RecordsetClone.MoveLast
        MsgBox "Last record."
        .btnNext.Enabled = False
    End If
    .btnPrevious.Enabled = True
End If
End With
End Sub

Private Sub btnPrevious_Click()
With Me
If Not .NewRecord Then
    .RecordsetClone.Bookmark = .Bookmark
    .RecordsetClone.MovePrevious
End If
If Not .RecordsetClone.BOF Then
    DoCmd.GoToRecord acForm, .Name, acPrevious
Else
    .RecordsetClone.MoveFirst
    MsgBox "First record."
    .btnPrevious.Enabled = False
End If
.btnNext.Enabled = True
End With
End Sub

Need to change the ControlTip text for btnNext.

Simply brilliant! Thank you sir.
 

Users who are viewing this thread

Top Bottom