Update Max value to another table

[pencuri]

I have a problem with to update with max value to another table..

I have 2 tables :

table A
- IDrpt (Auto Increment)
- pname
- qname

table B
- pID (Auto Increment)
- rptID
- itemno

quest:
I want, if someone fill table B (using form) the rptID on table B automatically update from IDrpt table A with Maximum value of IDrpt table A... so after input value on table B looks like this :

pID rptID itemno
1 9 cake 01
2 9 cake 02
3 9 cake 03

I have tried to write on SQL like this :
UPDATE tblB SET tblB.rptID = "(SELECT Max(tblA.IDrpt) FROM tblA)"
WHERE (((tblB.pID)=(SELECT (Max(pID)) AS PID FROM tblB)));

but not work, but if i wrote like this :
UPDATE tblB SET tblB.rptID = "9"
WHERE (((tblB.pID)=(SELECT (Max(pID)) AS PID FROM tblB)));

it's work... what wrong with my first SQL script?

nb: SQL script wrote as macro and run After Insert


rgds,

tedie