DMAX Data type mismatch

[armesca]

What is wrong with my code:

Private Sub Command20_Click()
Dim rs As DAO.Recordset
Dim year As Single


year = DMax("[Request Year]", "Request_Year")
MsgBox year
Set rs = CurrentDb.OpenRecordset(year)
With rs
.AddNew
![Request Year] = rs + 1
.Update
.Close
End With
Set rs = Nothing

I get a data type mismatch error in the bolded line, I am assuming it is a recordset problem. Any ideas?

 

[spikepl]

Data type mismatch is when you are trying to stuff something into an unsuitable container, like a brick into a bottle. The "stuffing" is the "=" sign, so the two sides don't match. Think about this.

What are you trying to do in the bolded line?

 

[spikepl]

Also, it is a bad idea to name variables, fields, tables etc using reserved names http://support.microsoft.com/kb/286335

"year" is a reserved name

And have a google on Access OpenRecordset and see what can go in as parameter for the OpenRecordset method

 

[gemma-the-husky]

also

![Request Year] = rs + 1

cannot be right (rs is the recordset!)

maybe this is what you need/meant

![Request Year] = year + 1

 

[armesca]

Thanks! I made a stupid mistake, I needed to replace rs + 1 with year. It now works. I also changed my year variable to a unreserved word.

 

[spikepl]

That you have tables called by the value of 'year' means you don't have normalized data. You should look into normalization.