Prefill a new record in a sub form

G

Guest

I am trying to prefill two fields on a subform, if it is a new record. Here
is what I used:
Private Sub Form_Current()
If Me.NewRecord = True Then
Dim varWeek As Variant

varWeek = DLookup("Max[Week#]", "Orders", "[EmpID] =" & Me.Parent.[EmpID])

Me.EmpID = Me.Parent.EmpID
Me.[Week#] = varWeek + 1

End If

End Sub

The subform fills based on the employee selection on the main form. When I
try to add a new record, I get the following error:
Microsoft Access can't find the field '|' referred to in your expression
Any suggestions would be great!

Thank you,
Renee
 
G

Guest

I misspelled my field name :)
Now the "can't find field" error is gone. The new error is:
Syntax error (missing operator) in query expression 'Max[Week#]'
 
D

DebbieG

I think you need parentheses around Week#

varWeek = DLookup("Max([Week#])", "Orders", "[EmpID] =" & Me.Parent.[EmpID])

And this should work if EmpID is numeric. If it is text then

varWeek = DLookup("Max([Week#])", "Orders", "[EmpID] = ' " & Me.Parent.[EmpID] & " ' ")

HTH,
Debbie


|I am trying to prefill two fields on a subform, if it is a new record. Here
| is what I used:
| Private Sub Form_Current()
| If Me.NewRecord = True Then
| Dim varWeek As Variant
|
| varWeek = DLookup("Max[Week#]", "Orders", "[EmpID] =" & Me.Parent.[EmpID])
|
| Me.EmpID = Me.Parent.EmpID
| Me.[Week#] = varWeek + 1
|
| End If
|
| End Sub
|
| The subform fills based on the employee selection on the main form. When I
| try to add a new record, I get the following error:
| Microsoft Access can't find the field '|' referred to in your expression
| Any suggestions would be great!
|
| Thank you,
| Renee
 
G

Guest

When I add parentheses around Week#: "Max([Week#])"
The error changes to: You canceled the previous operation
 
D

DebbieG

You might take a look at DMax:varWeek = DMax("[Week#]", "Orders", "[EmpID] = " & Me.Parent.[EmpID])HTH,Debbie"Renee"
| When I add parentheses around Week#: "Max([Week#])"
| The error changes to: You canceled the previous operation
|
| "DebbieG" wrote:
|
| > I think you need parentheses around Week#
| >
| > varWeek = DLookup("Max([Week#])", "Orders", "[EmpID] =" & Me.Parent.[EmpID])
| >
| > And this should work if EmpID is numeric. If it is text then
| >
| > varWeek = DLookup("Max([Week#])", "Orders", "[EmpID] = ' " & Me.Parent.[EmpID] & " ' ")
| >
| > HTH,
| > Debbie
| >
| >
| > |I am trying to prefill two fields on a subform, if it is a new record. Here
| > | is what I used:
| > | Private Sub Form_Current()
| > | If Me.NewRecord = True Then
| > | Dim varWeek As Variant
| > |
| > | varWeek = DLookup("Max[Week#]", "Orders", "[EmpID] =" & Me.Parent.[EmpID])
| > |
| > | Me.EmpID = Me.Parent.EmpID
| > | Me.[Week#] = varWeek + 1
| > |
| > | End If
| > |
| > | End Sub
| > |
| > | The subform fills based on the employee selection on the main form. When I
| > | try to add a new record, I get the following error:
| > | Microsoft Access can't find the field '|' referred to in your expression
| > | Any suggestions would be great!
| > |
| > | Thank you,
| > | Renee
| >
| >
| >
 
G

Guest

Thank you Debbie. I am still doing something wrong though, I just can't see
what. I changed to the DMax function:

varWeek= DMax("[Week#]", "Orders", "[EmpID] =" & me.Parent.[EmpID])
also tried:
varWeek= DMax("[Week#]", "Orders", "EmpID] =" & forms!frmOrders!EmpID)

Both still say: You canceled the previous operation
 
D

DebbieG

The only thing I can think of is that maybe the result is empty.

On the line below varWeek = type:

msgbox "value of varWeek = " & varWeek

to see what it is returning.

If it's nothing, you need to do something if it returns nothing like if isnull(varWeek).

HTH,
Debbie

| Thank you Debbie. I am still doing something wrong though, I just can't see
| what. I changed to the DMax function:
|
| varWeek= DMax("[Week#]", "Orders", "[EmpID] =" & me.Parent.[EmpID])
| also tried:
| varWeek= DMax("[Week#]", "Orders", "EmpID] =" & forms!frmOrders!EmpID)
|
| Both still say: You canceled the previous operation
|
| "DebbieG" wrote:
|
| > You might take a look at DMax:
| >
| > varWeek = DMax("[Week#]", "Orders", "[EmpID] = " & Me.Parent.[EmpID])
| >
| > HTH,
| > Debbie
| >
"Renee"
| > | When I add parentheses around Week#: "Max([Week#])"
| > | The error changes to: You canceled the previous operation
| > |
| > | "DebbieG" wrote:
| > |
| > | > I think you need parentheses around Week#
| > | >
| > | > varWeek = DLookup("Max([Week#])", "Orders", "[EmpID] =" & Me.Parent.[EmpID])
| > | >
| > | > And this should work if EmpID is numeric. If it is text then
| > | >
| > | > varWeek = DLookup("Max([Week#])", "Orders", "[EmpID] = ' " & Me.Parent.[EmpID] & " ' ")
| > | >
| > | > HTH,
| > | > Debbie
| > | >
| > | >
| > | > |I am trying to prefill two fields on a subform, if it is a new record. Here
| > | > | is what I used:
| > | > | Private Sub Form_Current()
| > | > | If Me.NewRecord = True Then
| > | > | Dim varWeek As Variant
| > | > |
| > | > | varWeek = DLookup("Max[Week#]", "Orders", "[EmpID] =" & Me.Parent.[EmpID])
| > | > |
| > | > | Me.EmpID = Me.Parent.EmpID
| > | > | Me.[Week#] = varWeek + 1
| > | > |
| > | > | End If
| > | > |
| > | > | End Sub
| > | > |
| > | > | The subform fills based on the employee selection on the main form. When I
| > | > | try to add a new record, I get the following error:
| > | > | Microsoft Access can't find the field '|' referred to in your expression
| > | > | Any suggestions would be great!
| > | > |
| > | > | Thank you,
| > | > | Renee
| > | >
| > | >
| > | >
| >
| >
| >
 
G

Guest

what line does the error occur.
is it on the before, on, or after the dmax?

Renee said:
Thank you Debbie. I am still doing something wrong though, I just can't see
what. I changed to the DMax function:

varWeek= DMax("[Week#]", "Orders", "[EmpID] =" & me.Parent.[EmpID])
also tried:
varWeek= DMax("[Week#]", "Orders", "EmpID] =" & forms!frmOrders!EmpID)

Both still say: You canceled the previous operation

DebbieG said:
You might take a look at DMax:varWeek = DMax("[Week#]", "Orders", "[EmpID] = " & Me.Parent.[EmpID])HTH,Debbie"Renee"
| When I add parentheses around Week#: "Max([Week#])"
| The error changes to: You canceled the previous operation
|
| "DebbieG" wrote:
|
| > I think you need parentheses around Week#
| >
| > varWeek = DLookup("Max([Week#])", "Orders", "[EmpID] =" & Me.Parent.[EmpID])
| >
| > And this should work if EmpID is numeric. If it is text then
| >
| > varWeek = DLookup("Max([Week#])", "Orders", "[EmpID] = ' " & Me.Parent.[EmpID] & " ' ")
| >
| > HTH,
| > Debbie
| >
| >
| > |I am trying to prefill two fields on a subform, if it is a new record. Here
| > | is what I used:
| > | Private Sub Form_Current()
| > | If Me.NewRecord = True Then
| > | Dim varWeek As Variant
| > |
| > | varWeek = DLookup("Max[Week#]", "Orders", "[EmpID] =" & Me.Parent.[EmpID])
| > |
| > | Me.EmpID = Me.Parent.EmpID
| > | Me.[Week#] = varWeek + 1
| > |
| > | End If
| > |
| > | End Sub
| > |
| > | The subform fills based on the employee selection on the main form. When I
| > | try to add a new record, I get the following error:
| > | Microsoft Access can't find the field '|' referred to in your expression
| > | Any suggestions would be great!
| > |
| > | Thank you,
| > | Renee
| >
| >
| >
Click to expand...
Click to expand...
 
G

Guest

Thank you guys! Debbie, you had posted the correction to my error:
The week# is a number, however the EmpID was text, and I did not have my
single/double quotes correct (as you had demonstrated!)

Thanks for your help :)

Have a great weekend,
Renee

visdev1 said:
what line does the error occur.
is it on the before, on, or after the dmax?

Renee said:
Thank you Debbie. I am still doing something wrong though, I just can't see
what. I changed to the DMax function:

varWeek= DMax("[Week#]", "Orders", "[EmpID] =" & me.Parent.[EmpID])
also tried:
varWeek= DMax("[Week#]", "Orders", "EmpID] =" & forms!frmOrders!EmpID)

Both still say: You canceled the previous operation

DebbieG said:
You might take a look at DMax:varWeek = DMax("[Week#]", "Orders", "[EmpID] = " & Me.Parent.[EmpID])HTH,Debbie"Renee"
| When I add parentheses around Week#: "Max([Week#])"
| The error changes to: You canceled the previous operation
|
| "DebbieG" wrote:
|
| > I think you need parentheses around Week#
| >
| > varWeek = DLookup("Max([Week#])", "Orders", "[EmpID] =" & Me.Parent.[EmpID])
| >
| > And this should work if EmpID is numeric. If it is text then
| >
| > varWeek = DLookup("Max([Week#])", "Orders", "[EmpID] = ' " & Me.Parent.[EmpID] & " ' ")
| >
| > HTH,
| > Debbie
| >
| >
| > |I am trying to prefill two fields on a subform, if it is a new record. Here
| > | is what I used:
| > | Private Sub Form_Current()
| > | If Me.NewRecord = True Then
| > | Dim varWeek As Variant
| > |
| > | varWeek = DLookup("Max[Week#]", "Orders", "[EmpID] =" & Me.Parent.[EmpID])
| > |
| > | Me.EmpID = Me.Parent.EmpID
| > | Me.[Week#] = varWeek + 1
| > |
| > | End If
| > |
| > | End Sub
| > |
| > | The subform fills based on the employee selection on the main form. When I
| > | try to add a new record, I get the following error:
| > | Microsoft Access can't find the field '|' referred to in your expression
| > | Any suggestions would be great!
| > |
| > | Thank you,
| > | Renee
| >
| >
| >
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

inserting new record, duplication error 2
Prefill data on a form. 1
Prefill a field 1
Before Update Event 3
Multiple criteria DLookup 3
Subform Current event firing twice with paradoxical Me keywordbehaviour 3
Datasheet Form problem 6
Form Subform DLookup 1

Top