DLookup

[richard]

I have created a line in code in a form for a command button from which I am
getting an error saying that I have a 'missing operator in the query
expression'. I created this by copying from Access Help and can see no
problems

All the fields referenced in the expression are numeric except SalesID which
is Autonumber

Could someone please help me with this

Private Sub cmdAddNewWithCurrentJob_Click()


DoCmd.GoToRecord , , acNewRec
Me.ProjectID = DLookup("[ProjectID]", "tblSalesDetails", "[SalesID] =" &
Me.[SalesID])

Exit_cmdAddNewWithCurrentJob_Click:
Exit Sub

Err_cmdAddNewWithCurrentJob_Click:
MsgBox Err.Description
Resume Exit_cmdAddNewWithCurrentJob_Click

End Sub


Thanks

Richard

 

[RonaldoOneNil]

The line before your DLookup is taking you to a new record, so is there any
value in the Me.SalesID and will this number exist in your tblSalesDetails
table ?

 

[richard]

Thanks for that, there is no number is me.SalesID as no information has been
recorded in the record yet. So I am going to use Dmax instead unles you have
any other suggestions

Richard

The line before your DLookup is taking you to a new record, so is there any
value in the Me.SalesID and will this number exist in your tblSalesDetails
table ?

I have created a line in code in a form for a command button from which I am
getting an error saying that I have a 'missing operator in the query
expression'. I created this by copying from Access Help and can see no
problems

All the fields referenced in the expression are numeric except SalesID which
is Autonumber

Could someone please help me with this

Private Sub cmdAddNewWithCurrentJob_Click()


DoCmd.GoToRecord , , acNewRec
Me.ProjectID = DLookup("[ProjectID]", "tblSalesDetails", "[SalesID] =" &
Me.[SalesID])

Exit_cmdAddNewWithCurrentJob_Click:
Exit Sub

Err_cmdAddNewWithCurrentJob_Click:
MsgBox Err.Description
Resume Exit_cmdAddNewWithCurrentJob_Click

End Sub


Thanks

Richard

 

[RonaldoOneNil]

DMax is fine as long as there are always records in your table as this will
also return null from an empty table.

Thanks for that, there is no number is me.SalesID as no information has been
recorded in the record yet. So I am going to use Dmax instead unles you have
any other suggestions

Richard

The line before your DLookup is taking you to a new record, so is there any
value in the Me.SalesID and will this number exist in your tblSalesDetails
table ?

I have created a line in code in a form for a command button from which I am
getting an error saying that I have a 'missing operator in the query
expression'. I created this by copying from Access Help and can see no
problems

All the fields referenced in the expression are numeric except SalesID which
is Autonumber

Could someone please help me with this

Private Sub cmdAddNewWithCurrentJob_Click()


DoCmd.GoToRecord , , acNewRec
Me.ProjectID = DLookup("[ProjectID]", "tblSalesDetails", "[SalesID] =" &
Me.[SalesID])

Exit_cmdAddNewWithCurrentJob_Click:
Exit Sub

Err_cmdAddNewWithCurrentJob_Click:
MsgBox Err.Description
Resume Exit_cmdAddNewWithCurrentJob_Click

End Sub


Thanks

Richard

 

[John W. Vinson]

I have created a line in code in a form for a command button from which I am
getting an error saying that I have a 'missing operator in the query
expression'. I created this by copying from Access Help and can see no
problems

All the fields referenced in the expression are numeric except SalesID which
is Autonumber

Could someone please help me with this

Private Sub cmdAddNewWithCurrentJob_Click()


DoCmd.GoToRecord , , acNewRec
Me.ProjectID = DLookup("[ProjectID]", "tblSalesDetails", "[SalesID] =" &
Me.[SalesID])

Exit_cmdAddNewWithCurrentJob_Click:
Exit Sub

Err_cmdAddNewWithCurrentJob_Click:
MsgBox Err.Description
Resume Exit_cmdAddNewWithCurrentJob_Click

End Sub


Thanks

As Renaldo says, this will fail on the new record because there is no SalesID.

What is your INTENTION? Which existing ProjectID do you want to insert on the
form? Or do you want to create a new ProjectID? If a new one, what should be
its value?