New Issue - Need some Help

[CalvinToo]

I have once again found I need to throw myself at the mercy of the forum. I'm hoping DK or one of the other guru's will assist my pitiful self once again as I continue to struggle with my existing DB I volunteered to fix at work (won't do that again)

Anyway I've been going along using the coding and learnings from my last post and I've hit another stumbling block.

In my front end user forms I'm trying to make them idiot proof (luckily I'm the perfect guy to test this as I'm an idiot and if it can be broke I'll find out how and in quick order).

The form I'm working with now has a my previous code [event procedure] in place so that if you open the form and hit the "Go To Last MT#" button it takes you to the last record which is great. The issue I have is that I need to force the front end user to ensure they have something in the "Product" and "QTY Sold" fields before been allowed to move to the next record using the "next record" arrow at the bottom of the form.

(click to expand Image)


Right now the front end user can just keep hitting the next record arrow and it will keep producing blank reports. I don't however want to force the front end user to fill in the TAG# as it won't always be neccessary just as AFE# and the check boxes won't be need in all cases.

Product pulls from "ProductID" which is from my "PurchaseOrder" table and is a descriptor . This field like "Contractor" and "Contact" are pull down tabs which pull from existing information in tables. So if the Purchase Order hasn't been entered and the ProductID field is blank it won't appear on the pull down and I don't want anyone to be able to move to the next record with out making a selection here.

The QTY Sold is also something that needs to be more then "Zero" or can not be null. I need to ensure that something is been transferred before allowing the front end user to create another Material Transfer Report.

So basically I have 5 fields that need to have information in them before allowing the front end user to move to the next record.

3 pull downs;

[Contractor]
[Contact]
[Product]

and two data entry fields

[Transferred To:]
[QTY sold]

All other fields can be blank
Thanks in advance to anyone willing to help out.

Cheers
Calvin

 

[RuralGuy]

Here's a good link that should help here. You would validate your controls in the BeforeUpdate event of the form.

 

[CraigDolphin]

One method is to put some form validation code in the on click event of the next record button.

Something like:

If Not Isnull(Me.Combo1) then
   If Not Isnull(Me.Combo2) then
      If Not Isnull(Me.Combo3) then
         If Not Isnull(Me.txtBox1) then
            If Not Isnull(Me.txtBox2) then
                'code to move to next record goes here
            End if
         End if
      End if
   End if
End if

Obviously, you need to substitute the correct control names etc. You may also wish to include some messages explaining to the user which field they need to fill in.

That said, there's many ways to skin this cat. Many of which are much more elegant.

[EDIT] Like RG's link, for example. Sorry RG.

 

[RuralGuy]

NP Craig. The more ideas the better.

 

[CalvinToo]

I'll try this out Craig thanks!

 

[CalvinToo]

Ok I entered the coding but and it is working somewhat. There are some issues though when I hit the "Go To Last MT#" Button I'm getting this error

[2645 Microsoft Office Access can't find the field "|" referred to in your expression]

This is the code I'm using.

Private Sub cmdMT_Click___Click()
On Error GoTo Err_cmdGotoNext_Click
Dim NumMRR As Integer
If Not IsNull(Me.MTTOCONTR) Then
If Not IsNull(Me.MTTONAME) Then
If Not IsNull(Me.[ProductID]) Then
If Not IsNull(Me.MTLOCATIONTO) Then
If Not IsNull(Me.[UnitsSold]) Then
NumMT = DCount("[MT#]", "MT")
If NumMT > 0 Then 'MT already exists so go to the last record
DoCmd.GoToRecord , , acLast
ElseIf NumMT = Me![MT#] Then 'user is on the last record
DoCmd.GoToRecord , , acNew
Else 'system error
MsgBox "System has experienced an error,"
End If
End If
End If
End If
End If
End If
Exit_cmdGotoNext_Click:
Exit Sub
Err_cmdGotoNext_Click:
Select Case Err.Number
Case 2105 'reached the end of the record so create a new one
MsgBox "You have reached the end of records. Creating a new one."
DoCmd.GoToRecord , , acNew
Case Else 'in case a different error ever pops up
MsgBox Err.Number & " " & Err.Description
End Select
Resume Exit_cmdGotoNext_Click
End Sub

When I compiled / debugged the code it came back "clean" so I'm not sure what I need to do next.

As far as the function of the form now when you move the mouse wheel the next record advances with the numbering which is ok but sadly it still keeps going on even if the fields I'm trying to "lock" are not filled. The coding is still not forcing the field entry?

Help!

Thanks
Calvin

 

[CraigDolphin]

First up, on a general level you might be better to look at the link RG gave you. You might also search in the example database forum to find an example db which traps the mouse wheel to avoid that kind of behavior. From memory it was written by Ghudson and is called a better mousetrap, or something like that.

More specifically though, I'm guessing that you might be running into issues with the line

NumMT = DCount("[MT#]", "MT")

As a general rule, you should avoid using special characters like # in field names. Errors like this may occur when you do.

You could also try putting a break point in the code and step though it to see exactly which line triggers the error.

But overall, I'm not sure what the point of the code is.

If the user is clicking on the goto last button, they are not intending to create a new record, so why force them onto one?

Here's how I'd go about this on your form.

Have separate cmd buttons for go to next, go to last, add new, previous, go to first.

Have a simple function in your form's code module to use for validating the form.

Public Function ValidateForm() as Boolean
ValidateForm = False
If Not IsNull(Me.MTTOCONTR) Then
   If Not IsNull(Me.MTTONAME) Then
       If Not IsNull(Me.[ProductID]) Then
          If Not IsNull(Me.MTLOCATIONTO) Then
             If Not IsNull(Me.[UnitsSold]) Then
                ValidateForm = True
            End if
         End if
      End if
   End if
End if
End Function


Private Sub cmdMTGoTo[COLOR="red"]Last[/COLOR]_Click()
On Error GoTo Err_cmdGotoLast_Click

If ValidateForm() = True then
   DoCmd.GoToRecord , , acLast
Else
   Msgbox "All required fields must be filled in before you can move to a new MT#",vbinformation
End if

Exit_cmdGotoLast_Click:
Exit Sub
Err_cmdGotoLast_Click:
MsgBox Err.Number & " " & Err.Description
Resume Exit_cmdGotoLast_Click
End Sub

Private Sub cmdMTGoTo[COLOR="Red"]Next[/COLOR]_Click()
On Error GoTo Err_cmdGotoNext_Click

If ValidateForm() = True then
   DoCmd.GoToRecord , , acNext
Else
   Msgbox "All required fields must be filled in before you can move to a new MT#",vbinformation
End if

Exit_cmdGotoNext_Click:
Exit Sub
Err_cmdGotoNext_Click:
MsgBox Err.Number & " " & Err.Description
Resume Exit_cmdGotoNext_Click
End Sub

And so on for each of the different command buttons.

 

[CalvinToo]

Thanks Craig! I'll try out the new coding and see if that works.

I found what I think the error was after changing "UnitsSold" to "UnitsTransferred" I thought I had it but it was actually my first line of coding that was causing the error

Private Sub cmdMT_Click___Click()

I had to remove the duplicate __Click and it fixed the pop up error however the form still doesn't work as intended.

I know the # symbol for table names is not the preferred way to do things but this DB is someone elses creation and they have so many tables and queries already in place and linked with this fields that I'm loathe to change them as I know the end result is spending the day fixing all the "relationships".

I did also look at the better mouse trap DB. I downloaded it and played around with it then I took the coding and tried to implement it and it didn't do anything that I could see. No errors but also no results either so I've removed the coding and tried the coding that I posted again.

If someone can point out to me how to implement the mouse trap coding I would certainly try it again but I couldn't get it to work (sorry I'm fairly new to visual basic and coding is my Achilles heel so to speak).

I'm also trying to make it as idiot proof as possible for the front end user and I thought having one button that moved them to the last record or perhaps a button that moved them to the next "open" ready to be made record would be the way to go vs many buttons that do many things (with the 3 buttons I have already "Go To last record", Preview Report and Print Report I thought I might be good).

I'll try out your suggestion though might be a better way to go!

Thanks
Calvin

 

[CraigDolphin]

Erk, yes. the sub name was a bit fouled up. Sorry I missed it!

And I haven't used the better mousetrap myself so haven't got any advice for you on implmenting that. If you search I'm sure you'll find plenty of threads about it though which may give you some insight.

 

[RuralGuy]

FWIW, the Better MouseTrap has been around quite a while and thoroughly tested with success by many. ghudson did a very good job with the code.

 

[CalvinToo]

RuralGuy,

I meant no disrespect to ghudson I'm sure the code is excellent and I take full responsibility for it not working... I'm 100% sure the error is on my part not his

Cheers
Calvin

 

[RuralGuy]

Correct me if I'm wrong but this appears to be a MainForm and a SubForm and the Navigation buttons appear to be on the MainForm while the "Product" and "QTY Sold" controls are on the SubForm. If this is true, what is creating the "reports" you refer to when using the Nav buttons? What causes a "report"?

 

[CraigDolphin]

Correct me if I'm wrong but this appears to be a MainForm and a SubForm and the Navigation buttons appear to be on the MainForm while the "Product" and "QTY Sold" controls are on the SubForm. If this is true, what is creating the "reports" you refer to when using the Nav buttons? What causes a "report"?

RG, did this end up on the wrong thread or did I miss something entirely?

 

[RuralGuy]

Sorry Craig,
I had re-read the 1st post in the thread and was wondering if maybe I didn't take off in the wrong direction with my 1st post. I think I should have covered a few bases first with the questions I finally posted. Or maybe I'm just all wet on this.

 

[CraigDolphin]

Ah, ok. Perhaps I should re-read the OP more carefully too

And now that I look at that image, I see another possible approach.

Instead of using four buttons to navigate records, why not use one navigation dropdown/combo (that lists existing MT#'s) that shifts to that record when a # is selected, and one button to add a new MT.

So few cats, so many flaying techniques to try

 

[dkinley]

I don't know if this will work in your situation, I avoid the whole mouse issue by setting a particular form property.

In properties -> Other -> Cycle -> Current Record.

This will only show the current record. I am allowed to use this exclusively because when users navigate in my applications, they go to one record and one record only (not allowed to hit buttons and go to the next).

My philosophy is why are they cycling through thousands of records looking for a certain one? If I don't let them do it, they don't waste time. They get to a specific record each time through record limiting find features and clicking on the record they need.

-dk

 

[CalvinToo]

Ah, ok. Perhaps I should re-read the OP more carefully too

And now that I look at that image, I see another possible approach.

Instead of using four buttons to navigate records, why not use one navigation dropdown/combo (that lists existing MT#'s) that shifts to that record when a # is selected, and one button to add a new MT.

So few cats, so many flaying techniques to try

Craig,

I thought about the drop down / combo but I think by the end of projects they will likely have several thousand transfer reports and I want them to be able to see not just the number but also who it went to and what the product was by scrolling through reports that have been previously generated. I also wanted them to be able to jump to the last record or go to next either/or and even both as you have now got me working on currently.

DK,
Thanks for the post. I want to keep the ability of allowing the front end users of looking at the previous records using either the mouse wheel or the record "arrows" on the bottom of the form.

I think I just need to ensure I have some data in my drop down boxes to avoid errors in my testing. I'm just trying that now.

Cheers
Calvin

 

[CraigDolphin]

Obviously you know what UI works best for your needs.

FYI, don't forget you can display more than one column of information in your combo's rowsource.

Thus when they click the drop down arrow, they could see one column with the MT#, a second column with the destination, and a third with the product. Of course, only one value can be stored, but you can display more than one piece of information in the drop down list itself.

Maybe that's not the way you want to go now, but perhaps it is something to keep in mind for the future.

 

[CalvinToo]

Thanks Craig. I appreciate any suggestions and help here. I'm learning as I go here he-he pretty overwhelming at times oh and maddening

Right now I'm working on getting the information from the other forms to populate the drop down selection boxes on this form before I can test for other issues. Once I'm done fighting with that I'm sure I'll find another issue...sigh...

Thanks again though I appreciate the posts from everyone. As far as getting the columns to display mulitple fields I'm still working on getting one field in some cases.

Cheers
Calvin

 

[dkinley]

Hey Cal ... not sure if this will help on drop-downs ... but I did a little diagram on this thread ...

http://www.access-programmers.co.uk/forums/showthread.php?t=155344&page=2

It has some notes in the text of that post. Although it is for something unrelated, the bit about the drop-down boxes might be some help.

Worst case, it's something you could print off should you need a memory jog.

-dK