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( )

[CODE=vb]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_GetLineNumb er

Set rs = F.Recordset.Clo ne

' Find the current record.
Select Case rs.Fields(KeyNa me).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_GetLineNumb er:
' Return the result.
GetLineNumber = CountLines

Exit Function

Err_GetLineNumb er:
CountLines = 0
Resume Bye_GetLineNumb er

End Function[/CODE]

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("YourFormNameGo esHere","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