New Issue - Need some Help

Ok I've worked my way through getting the forms to populate the drop down boxes and I have the coding in that shows the error messages if I hit either button when the form is empty. However the (contact field gives me the
2465 MS Access can't find the field "|" referred to in your expression
Click to expand...
error!

Here is the screen shot of the properties of the "drop box" that gives me the error (click to enlarge)



I have the query builder set up to find the contact name from my original form "contractor" which the front end user populates when a new contractor starts to work. They enter all the information in this form and then I'm asking the MT form to pull the information from here



Any idea where I've gone wrong here my logic is failing me :S

Thanks
Calvin
 
I have no idea off the top of my head .. I noticed you still used MT#. That may or may not be causing an error (now or in the future).

First recommendation is to change that to MTNumber or something. I know, all those gosh darn changes need to be done throughout database. =[

My previous post may help in the area of combo boxes.

-dK
 
Well, not sure what's triggering the error but your row source query doesn;t make any sense to me.

You have two tables in the grid. You want all records from one table, regardless of whether there's a corresponding record in the second table. But you're only retrieving records from the second table.

Why not simply discard the first table and the funky join, and simply get the records from the second table if that's all you want?
 
Well ... now that I really look, you have ContractNo linked to MTTONAME. My first question is if ContractNo is a Yes/No field .. or is No short for Num or Number?

Whether it's a Yes/No field or Number field MTTONAME certainly sounds like a Text field. I've never been able to link two different type fields.

For this type of use, link the correct and corresponding foreign key to forge the link.

For instance - the Contractor table has ContractorID and MT table has MTID ... , but where is fkContractorID for the MT table (where do you store the contractor ID in table MT for the link?)

-dK
 
dk,

It is a number (phone number with input mask actually) but as such it is a text field so I could use the input mask. I've removed the link and actually removed everything just leaving the contact field as a blank text box so that the front end user can just type in any name for the company representitive which makes more sense to me due to high staff turn over.

However even with all the constraints removed it is still returning the MSG "All Required fields must be filled in before you can move to a new MT#" despite the fact I have all the "needed" fields filled?

I must still have any error somewhere in the coding or a property set wrong...

Thanks
Calvin
 
Hard to say without seeing the code and properties since you made the changes. Or better yet, a sample db.
 
I agree with Craig ...

For that type of error, I would doubly and triply ensure that ALL required fields are being met by looking at the table and the properties there. Also making sure you're meeting your relationship obligations.

You could have 2 or more errors "ping-ponging" - you fix something to cause an error to fix that error and recause the first one. This effect could be caused by setting up one end using one method and the other using a different method and they just aren't meeting in the middle.


-dK

Edit: The reason I stress primary/foreign key in the previous post for linking is because user's do not get to mess with this field. You had the link set using a phone number. Who is to say that in one form the user enter's phone number X and in the other phone number Y and you expect them to magically link. Using the PK/FK link will guarentee the best results and minimize potential sources of corruption.
 
Last edited:
Here is the coding I'm working with.

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.[UnitsTransferred]) Then
ValidateForm = True
End If
End If
End If
End If
End If
End Function

Private Sub cmdMTGoToLast_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 cmdMTGoToNext_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

Private Sub Form_Current()
If Me.NewRecord Then
Me![MT#] = DMax("[MT#]", "MT") + 1
End If
End Sub
Private Sub Command59_Click()
On Error GoTo Err_Preview_Click
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
If IsNull(Me![MT#]) Or Me![MT#] = 0 Then
MsgBox "Enter Material Transfer Information before previewing Report"
Else
DoCmd.OpenReport "MTReport", acPreview, , "[MT].[MTID]=" & Me![MTID]
End If
Exit_Preview_Click:
Exit Sub
Err_Preview_Click:
If Err <> 2501 Then
MsgBox Err.Description
End If
Resume Exit_Preview_Click
End Sub
Private Sub Command61_Click()
On Error GoTo Err_Print_Click
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
If IsNull(Me![MT#]) Or Me![MT#] = 0 Then
MsgBox "Enter Materials Transfer Information before Printing Report"
Else
DoCmd.OpenReport "MTReport", acPrint, , "[MT].[MTID]=" & Me.MTID
End If
Exit_Print_Click:
Exit Sub
Err_Print_Click:
If Err <> 2501 Then
MsgBox Err.Description
End If
Resume Exit_Print_Click
End Sub
Click to expand...

I'll post the properties next.

Thanks guys!

Cheers
Calvin
 
Here are the Properties of the "buttons" both cmds go back to the coding posted above.

(Click to enlarge)



Hopefully this is enough but let me know if you need anything else (my head is spinning from looking at this all day)

Cheers
Calvin

 
dkinley said:
I like how he posts a book and throws that on top. =]
Click to expand...


I know I owe you huge - I will definetly be buying a you a drink or two if I ever go through your neck of the woods in my travels :cool:
 
Heheh .. is all good.

Okay man .. this thing ran fine for me ... but I did notice a couple of things.

First, your command buttons have a "MT" in the name, but references for the errors do not ...

Your ...
Code: 
Private Sub cmdMTGoToLast_Click()
On Error GoTo Err_cmdGotoLast_Click
...

Should be ...
Code: 
Private Sub cmdMTGoToLast_Click()
On Error GoTo Err_cmdMTGotoLast_Click
...
and so on and so forth.

Anyhow .. not an issue ... but try this technique

Take your ...
Code: 
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.[UnitsTransferred]) Then
                            ValidateForm = True
                        End If
                    End If
                End If
            End If
        End If
End Function

And make it look like this ...

Code: 
Public Function ValidateForm() As Boolean
    ValidateForm = False
                        If Not IsNull(Me.[UnitsTransferred]) Then
                            ValidateForm = True
                        End If
End Function

If that works, then add the next If/Then wrapper around it. I am suspecting a reference. I would name the form controls something I would recognize. Like this example, the control, UnitsTransferred, would be named txtUnitsTransferred then my line would look like Me!txtUnitsTransferred.

Anyhow .. start like that and work backwards - should be able to pinpoint it.

-dK
 
It may be that the way you've left in the square brackets in the validation function is telling access to look at the field that the control is bound to, rather than the control itself. And since the record has possibly not been saved to the table yet, there's nothing in the field yet.

If so then

Code: 
Public Function ValidateForm() As Boolean
ValidateForm = False
If Not IsNull(Me.MTTOCONTR) Then
If Not IsNull(Me.MTTONAME) Then
If Not IsNull(Me.[COLOR="Red"]ProductID[/COLOR]) Then
If Not IsNull(Me.MTLOCATIONTO) Then
If Not IsNull(Me.[COLOR="red"]UnitsTransferred[/COLOR]) Then
ValidateForm = True
End If
End If
End If
End If
End If
End Function

I'm assuming that the field and the control names are identical in your form. If the control name differs, you need to change the bits in red to reflect the actual control name.

When you're typing the Me.Controlname you ought to see the intellisense list showing you the correct names for the controls and other form properties.
 
And Dkinley's suggestion for pinpointing the problem is good advice :)
 
I have to do it like that or MsgBox popups to tell me stuff in mid-execution.

I don't know how to use Debug.Print or Immediate Window stuff .. that thar is fer xpurts! =]

-dK
 
Dk,

I think your a genius. Anyway I made the changes more or less and removed these "wrappers" as with them in I was still getting errors

If Not IsNull(Me.MTTONAME) Then
ValidateForm = True
End If
If Not IsNull(Me.[ProductID]) Then
ValidateForm = True
End If
If Not IsNull(Me.MTLOCATIONTO) Then
ValidateForm = True
End If
If Not IsNull(Me.[UnitsTransferred]) Then
ValidateForm = True
End If
Click to expand...

This is the remaining code and it appears to work - Now I just need to re-introduce the coding above step by step to force the front end user to fill in those remaining 4 fields correct?

Existing (and working) new code.

Public Function ValidateForm() As Boolean
ValidateForm = False
If Not IsNull(Me.MTTOCONTR) Then
ValidateForm = True
End If

End Function

Private Sub cmdMTGoToLast_Click()
On Error GoTo Err_cmdMTGoToLast_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_cmdMTGoToLast_Click:
Exit Sub
Err_cmdMTGoToLast_Click:
MsgBox Err.Number & " " & Err.Description
Resume Exit_cmdMTGoToLast_Click
End Sub
Private Sub cmdMTGoToNext_Click()
On Error GoTo Err_cmdMTGoToNext_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_cmdMTGoToNext_Click:
Exit Sub
Err_cmdMTGoToNext_Click:
MsgBox Err.Number & " " & Err.Description
Resume Exit_cmdMTGoToNext_Click
End Sub

Private Sub Form_Current()
If Me.NewRecord Then
Me![MT#] = DMax("[MT#]", "MT") + 1
End If
End Sub
Private Sub Command59_Click()
On Error GoTo Err_Preview_Click
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
If IsNull(Me![MT#]) Or Me![MT#] = 0 Then
MsgBox "Enter Material Transfer Information before previewing Report"
Else
DoCmd.OpenReport "MTReport", acPreview, , "[MT].[MTID]=" & Me![MTID]
End If
Exit_Preview_Click:
Exit Sub
Err_Preview_Click:
If Err <> 2501 Then
MsgBox Err.Description
End If
Resume Exit_Preview_Click
End Sub
Private Sub Command61_Click()
On Error GoTo Err_Print_Click
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
If IsNull(Me![MT#]) Or Me![MT#] = 0 Then
MsgBox "Enter Materials Transfer Information before Printing Report"
Else
DoCmd.OpenReport "MTReport", acPrint, , "[MT].[MTID]=" & Me.MTID
End If
Exit_Print_Click:
Exit Sub
Err_Print_Click:
If Err <> 2501 Then
MsgBox Err.Description
End If
Resume Exit_Print_Click
End Sub
Click to expand...

Now I should be able to pin point which piece of the puzzle is causing the issue by testing each field one at a time I hope.

Cheers
Calvin
 
Correct. Make sure you read Craig's post where text is highlighted in red.

Add them back. In terms of CPU usage and application speed, the big If-Then block should execute faster than breaking them out one by one.

-dK
 
It appears that ProductID is the trouble maker it is the one returning the error "2465"

Here is a look at the properties. I'm not sure but I think it causing the error due to the tables?



I think I'm getting closer to locking this one down :)

Thanks again guys!
Cheers
Calvin
 
Okay ... so you have a few data validation checking for something on a form but one of them is checking a control on a subform?

-dK
 
I should post this combo box as well for the "ProductID" in the field. I'm not sure what to look for ?

 
You must log in or register to reply here.

Similar threads

J
Solved Not a coding problem
Replies
6
Views
292
gemma-the-husky
gemma-the-husky
R
Solved Need help with displaying ComboBox contents (hyperlink involved)
Replies
7
Views
323
theDBguy
theDBguy
A
Including a sub-form in a form triples the number of records - why?
Replies
8
Views
249
akedm
A
E
Sum of values from subform to table
Replies
16
Views
351
Pat Hartman
P
J
JTBrown New Member of the Forum
Replies
6
Views
258
JTBrown
J

Users who are viewing this thread

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