Get Line Number ()

[petemul]

Hi all,

I am having a problem with a PO database. I am trying to create a field that auto generates a PO line number. I have create a module (given in microsoft office help). When I try to use this function in a text box on the sub form I get an error " the object doesnot contain the automation object "Get Line Number" and #Name? is displayed.

the command in the control source is =GetLineNumber([Form],"ID",[ID])

ID being an auto number unique to the line item in the table.

Help please

[Banfa]

This is not the correct forum for this POST it should be in a relevent database forum.

For now I am moving it to Access (because that seems to be quite popular) but you are unlikely to get a satisfactory answer until you tell us what sort of database you are using.

Please read the Posting Guidelines.

Banfa
Administrator

[petemul]

Thanks - as you have prob notice I'm new to this. The database i'm working in is microsoft Access 2003.

[missinglinq]

You'll really need to give us a better explanation of what you're trying to do if we're to help you!

For instance, what's a PO number/PO Line number? Post Office? Purchase Order? And exactly what are you trying to accomplish?

We also need the code for the function GetLineNumber().

Welcome to TheScripts!

Linq ;0)>

[petemul]

You'll really need to give us a better explanation of what you're trying to do if we're to help you!

For instance, what's a PO number/PO Line number? Post Office? Purchase Order? And exactly what are you trying to accomplish?

We also need the code for the function GetLineNumber().

Welcome to TheScripts!

Linq ;0)>

Ok

Purchase order line number - I am trying to auto number the line items on a subform.

I have used this module to obtain the function GetLineNumber( )

Option Compare Database
Option Explicit
 
'============================================================
' The following function is used by the subLineNumber form
'============================================================
 
Function GetLineNumber(F As Form, KeyName As String, KeyValue)
   Dim rs As Object
   Dim CountLines As Integer
 
   On Error GoTo Err_GetLineNumber
 
   Set rs = F.Recordset.Clone
 
   ' Find the current record.
   Select Case rs.Fields(KeyName).Type
      ' Find using numeric data type key value?
      Case adSmallInt, adTinyInt, adBigInt, adInteger, adDecimal, adNumeric, adCurrency, adSingle, adDouble
         rs.FindFirst "[" & KeyName & "] = " & KeyValue
      ' Find using date data type key value?
      Case adDate
         rs.FindFirst "[" & KeyName & "] = #" & KeyValue & "#"
      ' Find using text data type key value?
      Case adChar, adVarChar
         rs.FindFirst "[" & KeyName & "] = '" & KeyValue & "'"
      Case Else
         MsgBox "ERROR: Invalid key field data type!"
         Exit Function
   End Select
 
   ' Loop backward, counting the lines.
   Do Until rs.BOF
      CountLines = CountLines + 1
      rs.MovePrevious
   Loop
 
Bye_GetLineNumber:
         ' Return the result.
         GetLineNumber = CountLines
 
         Exit Function
 
Err_GetLineNumber:
         CountLines = 0
         Resume Bye_GetLineNumber
 
End Function

I then placed a unbound textbox in the subreport of a purchase order form. (the subreport is the line items).

in the control I used this; =GetLineNumber([Form],"ID",[ID]

I have taken all of this from a microsoft sample database. I have tried renaming forms, ID etc.

In the Purchase order Line table ID is an auto number field (primary field) for all the records.
I have also moved this text field to the first row of the tab order. The error I get in the box is #Name?

I think this is as much as I know / capable of hope its a bit clearer now.

Thanks again

[missinglinq]

I don't have time to go into this in detail right now, but the one thing that stands out in a quick scan is your line

in the control I used this; =GetLineNumber([Form],"ID",[ID]

You have to actually insert the name of your form here, in quotes:

=GetLineNumber("YourFormNameGoesHere","ID",[ID])

There may be other issues, but that's a start. I'll look back later, when I get a chance, but try that for now. In the meantime, maybe someone else will take a look at it for you.

Linq ;0)>

[petemul]

Yes, that was my inital thought but when I put a name in there I get the error "The object does not contain the automation object 'GetLineNumber'.

I have tried every verison of form names, query name (of which the form is based), table name.

In the example form I saw this on it was actual [Form] that used not the form name.

http://support.microsoft.com/kb/325236/en-us

is the link to where I saw this and have taken the code from.

Any way thanks for your help - there's no rush it puzzled me for weeks so I'd pretty well given up on it anyway.

[nico5038]

Assuming it's code "behind" the form needed by the function you could pass the form object by using:
=GetLineNumber(Me,"ID",[ID])
as Me points to the form.

Nic;o)

[Scott Price]

Take a look again at line 9: This needs to be Dim rs As DAO.Recordset (as in the example from the MS website you posted), not As Object.

And then, as Linq suggested, you need to put the form's name into the parameters you pass.

Regards,
Scott

[Scott Price]

Also, I noticed that you have changed the data types in the Select Case statement. Is this for purposes of working with VB6? The original data types in the example on MS's website are more than enough for working with VBA.

Another point is that you should close the recordset at the end of your code block, as leaving them open can tend to cause problems later on.

rs.Close
Set rs = Nothing

Should be included in any code that opens a recordset.

Regards,
Scott

[petemul]

Thanks for that - still have the same error though. I've typing it in VB as opposed to cut and paste in case other errors had transferred over.

Must be an issue some where else might try making a small simple database and seeing if it works there. I let you know the outcome.

[petemul]

The changes you have noticed are from the VB script of the sample database that this command was working in. I thought the diffences in versions may make a difference so I have tried both. I just didn't realise I'd left the old version in my database when i copied and pasted my vb on this page.

I have to be honest and say I now very little about VB so technically its a bit above me.

Thanks for your time

PT

[nico5038]

Hmm, when you want to solve this without code you could use a query with the DCOUNT() function like:

select Dcount("ID","query_of_subform","POnumber=" & [POnumber] & " AND [ID} <=" & [ID]) as LineCountPO, POnumber, ID, .... from tblPurchaseOrders;
 

This will create a linenumber in the query and you can place the [LineCountPO] "straight away" in your subform.

Nic;o)

[missinglinq]

Maybe you've said and I missed it, if so, forgive me, but exactly where do you have the GetLineNumber() function? Is it in the code for the main form, the code for the subform or in a standard module?

Linq ;0)>

[petemul]

Linq,

What I have done is created GetLineNumber() in a module. Then on the subform I have placed a text box in in the control source I have used the expression builder to use the function "GetLineNumber()" which is now available to select. The expression gives
GetLineNumber(<<F>>,<<KeyName>>,<<KeyValue>>)

I then put in the relevant fields and then the problem starts.

PT

[petemul]

Hmm, when you want to solve this without code you could use a query with the DCOUNT() function like:

select Dcount("ID","query_of_subform","POnumber=" & [POnumber] & " AND [ID} <=" & [ID]) as LineCountPO, POnumber, ID, .... from tblPurchaseOrders;
 

This will create a linenumber in the query and you can place the [LineCountPO] "straight away" in your subform.

Nic;o)

Nico

Forgive my ignorance but in this example are you wwriting this code in a new module? or in an event procedure or in the query its self?

To be honest I think I should just forget this as I think my serious lack of programing skills may be wasting your time!

I think I need to go and have some training as opposed to self taught

Thanks

PT

[missinglinq]

What I have done is created GetLineNumber() in a module.

You didn't, by chance, also name the module GetLineNumber? People often do this, and it confuses the Access gnomes! If you have, you need to rename the module.

Linq ;0)>

[petemul]

You didn't, by chance, also name the module GetLineNumber? People often do this, and it confuses the Access gnomes! If you have, you need to rename the module.

Linq ;0)>

Yes I did - changed it and it works!!!

Thank you for persistance

[missinglinq]

Glad we finally figured it out!

Linq ;0)>

[nico5038]

Nico

Forgive my ignorance but in this example are you wwriting this code in a new module? or in an event procedure or in the query its self?

To be honest I think I should just forget this as I think my serious lack of programing skills may be wasting your time!

I think I need to go and have some training as opposed to self taught

Thanks

PT

My sample is, like posted, purely a query and requires no code.
The Dcount() function can be used in a query and will give the linenumber because it counts the number or rows for the specified key and only those where the "subkey" is less or equal.

Good find that modulename linq !
Hard for me to figure, as I always start my modulenames with "mod" :-)

Nic;o)

[missinglinq]

Unless you teach or spend an awful lot of time in forums like this one, it can be easy to forget the very basic mistakes that many people make. Being in forced retirement, I have the time to contribute here, as well as to four other Access forums, on a regular basis. This is the 4th thread I've seen today where the problem was having the module and function named the same!

Linq ;0)>

[vdzr]

what about the new record?
what is the iif statement for : don't show a linenumber for a new record?

I wanted a "x . y" number and changed the textboxcontrolsource to

=IIf([newrecord]=True;"";GetLijnNr([Form];"PrimIDTstblwoningen";[PrimIDTstblwoningen]) & " . " & Count(*))

[Scott Price]

vdzr,

You have asked your question in another member's thread. This is called thread hijacking and is not allowed on this site. There are various reasons for this, the principal of which is that it typically diverts the flow of the thread away from his needs and into yours. Another is that it will tend to mean that your new question is not seen as such, and may get overlooked by many of our experts (You appreciate that they're not looking for answered questions).
Please post your questions in their own threads in future (See How to Ask a Question).

MODERATOR