Error 3265

[Guest]

I am trying to use a field in a query to fill a txtfield when I open a form.
The txtbox is 'txtMaxRecordIntblEMEARepairs' I have a query
'qryMaxRepairRecord' that is simply looking at a table and finding the max
value in a field called 'fldRepairRecordNumber' which is an Auto number. I
have used the following code before, also "on Open' event of form, and no
problem. Now I am getting an error 3265, " Item cannot be found in the
collection correesponding to the requested name or Ordinal".
I am sure the txtname and fldname are correct.

What am I doing wrong.

'Opening Connection, set Variables, Open Table
Dim Conn As ADODB.Connection
Dim rst As ADODB.Recordset
Set Conn = New ADODB.Connection
Conn.Open CurrentProject.Connection
Set rst = New ADODB.Recordset
rst.Open "qryMaxRepairRecord", Conn, adOpenStatic, adLockReadOnly
'Move to first record
rst.MoveFirst
Me!txtMaxRecordIntblEMEARepairs = rst!fldRepairRecordNumber
'Close qryMaxRepairRecord
rst.Close
Set rst = Nothing
Set NewConn = Nothing

 

[Guest]

Hi,
why so complicated?
Couldn't you just use a dmax function to determine the max value of this
field and then do whatever you want with it.
Look at this for more info on dmax:
http://www.techonthenet.com/access/functions/domain/dmax.php
Furthermore, what are you trying to achieve.
I assume the autonumber is a primary key field. Primary key values should be
of NO meaning to you or your users and shouldn't even be displayed. It is
only for access to work internally correct.
Additionally use the on load event of the form and not on open!
HTH
Good luck

 

[Guest]

Oliver, Thankyou. Your right, simple and easy. worked fine!

However

for future reference, what am I doing wrong. The auto field is not the key
to the table and like I said "I used this code before"?

Thanks

Joe

 

[Guest]

Hi,
did you try the on load event instead.
Furthermore I don't think you want to use an autonumber for this then.
An autonumber doesn't mean it will be 100% sequential. Sometimes it skips
numbers or leaves some out. It is really just for access to work internally
as it ensures that each record is unique no matter what. Plus it will be
affected by compact and repair and I don't know if you want that to happen.
You should try to use some other sequential numbering system with a dmax+1
formula.
HTH
Good luck

 

[Guest]

I did try to use the 'On Load' event. Same problem.

Now to the autonumber. You have me thinking. Like you said, I want to
create a unique number to be used for an "RMA Number". When I use an auto
number, the number will stay the same , won't it. Even if I compact. The
autonumber may skip but still remain with the same record, wont it?

Could you expand on your Dmax +1 thought?

Thanks for your time.

 

[Guest]

Hi,
no the autonumber will get messed up. A compact and repair will take out
wholes in the numbering and apply numbers which it didn't use before because
of deletions or...! This way your numbering will get totally messed up.
Instead use the dmax function to create this unique sequential value.
To generate a sequential increasing number you could use:

Nz(Dmax("YourIDField","YourTableName"),0)+1

HTH
Good luck

 

[Guest]

Thanks, I'll give it a try.

 

[Guest]

Nz(Dmax worked great. I can work with this.

I still wish I could figure oiut what was wrong with the code?

Thanks again

 

[Douglas J Steele]

What is the actual SQL of qryMaxRepairRecord?

See whether

Me!txtMaxRecordIntblEMEARepairs = rst.Fields(0)

works.