Database Problems (1 Viewer)

R

rayape

Registered User.
Local time
Yesterday, 21:02
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

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 said:
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
Click to expand...


@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.
 
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.
 
CJ_London said:
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.
Click to expand...

Thank you sir. Will fix them all.

Please let me know where to use the code.
 
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
 
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.
 
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?
 
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.
 
@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?
 
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.
 
June7 said:
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.
Click to expand...
Ah OK, I misunderstood. :(
I thought it was meant to be a generic routine for form navigation.
 
June7 said:
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.
Click to expand...

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.
 
If you want to provide db for analysis, follow instructions at bottom of my post.
 
@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

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
 
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:-

Next Previous Quit – Nifty Access

www.niftyaccess.com www.niftyaccess.com
 
Last edited:
@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

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.
 
June7 said:
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.
Click to expand...

Simply brilliant! Thank you sir.
 
You must log in or register to reply here.

Similar threads

D
Google search
Replies
1
Views
173
Dave E
D
D
Call a button's action from VBA
Replies
4
Views
199
Dave E
D
H
Handling Extensive Maths in MS Access
Replies
17
Views
562
haxan786
H
P
Use form filter text in SQL
2
Replies
25
Views
514
pdanes
P
O
Solved Unbound Form, Two Subforms: Checkbox Selection for Product-Hotel Association
Replies
7
Views
307
Odeh Naber
O

Users who are viewing this thread

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