Error 3265

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

NNlogistics - 09 Jan 2006 15:54 GMT

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

Signature

Thanks for any assistance

Reply to this Message

freakazeud - 09 Jan 2006 15:58 GMT

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

Signature

Oliver
Admin Specialist & Computer Science Major @ UMD - Go Terps - :)
http://www.oli-s.de

> 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
[quoted text clipped - 21 lines]
> Set rst = Nothing
> Set NewConn = Nothing

Reply to this Message

NNlogistics - 09 Jan 2006 16:35 GMT

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

Signature

Thanks for any assistance

> Hi,
> why so complicated?
[quoted text clipped - 35 lines]
> > Set rst = Nothing
> > Set NewConn = Nothing

Reply to this Message

freakazeud - 09 Jan 2006 16:45 GMT

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

Signature

Oliver
Admin Specialist & Computer Science Major @ UMD - Go Terps - :)
http://www.oli-s.de

> Oliver, Thankyou. Your right, simple and easy. worked fine!
>
[quoted text clipped - 46 lines]
> > > Set rst = Nothing
> > > Set NewConn = Nothing

Reply to this Message

NNlogistics - 09 Jan 2006 17:01 GMT

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.

Signature

Joe

> Hi,
> did you try the on load event instead.
[quoted text clipped - 58 lines]
> > > > Set rst = Nothing
> > > > Set NewConn = Nothing

Reply to this Message

freakazeud - 09 Jan 2006 17:11 GMT

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

Signature

Oliver
Admin Specialist & Computer Science Major @ UMD - Go Terps - :)
http://www.oli-s.de

> I did try to use the 'On Load' event. Same problem.
>
[quoted text clipped - 69 lines]
> > > > > Set rst = Nothing
> > > > > Set NewConn = Nothing

Reply to this Message

NNlogistics - 09 Jan 2006 18:44 GMT

Thanks, I'll give it a try.

Signature

Thanks for any assistance

> Hi,
> no the autonumber will get messed up. A compact and repair will take out
[quoted text clipped - 81 lines]
> > > > > > Set rst = Nothing
> > > > > > Set NewConn = Nothing

Reply to this Message

NNlogistics - 09 Jan 2006 18:57 GMT

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

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

Thanks again

Signature

Thanks for any assistance

> Hi,
> no the autonumber will get messed up. A compact and repair will take out
[quoted text clipped - 81 lines]
> > > > > > Set rst = Nothing
> > > > > > Set NewConn = Nothing

Reply to this Message

Douglas J Steele - 09 Jan 2006 21:23 GMT

What is the actual SQL of qryMaxRepairRecord?

See whether

Me!txtMaxRecordIntblEMEARepairs = rst.Fields(0)

works.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Nz(Dmax worked great. I can work with this.
>
[quoted text clipped - 87 lines]
> > > > > > > Set rst = Nothing
> > > > > > > Set NewConn = Nothing

Reply to this Message