By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,165 Members | 850 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,165 IT Pros & Developers. It's quick & easy.

Get Line Number ()

P: 10
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
Oct 5 '07 #1
Share this Question
Share on Google+
22 Replies


Banfa
Expert Mod 5K+
P: 8,916
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
Oct 5 '07 #2

P: 10
Thanks - as you have prob notice I'm new to this. The database i'm working in is microsoft Access 2003.
Oct 5 '07 #3

missinglinq
Expert 2.5K+
P: 3,532
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)>
Oct 5 '07 #4

P: 10
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( )

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. '============================================================
  5. ' The following function is used by the subLineNumber form
  6. '============================================================
  7.  
  8. Function GetLineNumber(F As Form, KeyName As String, KeyValue)
  9.    Dim rs As Object
  10.    Dim CountLines As Integer
  11.  
  12.    On Error GoTo Err_GetLineNumber
  13.  
  14.    Set rs = F.Recordset.Clone
  15.  
  16.    ' Find the current record.
  17.    Select Case rs.Fields(KeyName).Type
  18.       ' Find using numeric data type key value?
  19.       Case adSmallInt, adTinyInt, adBigInt, adInteger, adDecimal, adNumeric, adCurrency, adSingle, adDouble
  20.          rs.FindFirst "[" & KeyName & "] = " & KeyValue
  21.       ' Find using date data type key value?
  22.       Case adDate
  23.          rs.FindFirst "[" & KeyName & "] = #" & KeyValue & "#"
  24.       ' Find using text data type key value?
  25.       Case adChar, adVarChar
  26.          rs.FindFirst "[" & KeyName & "] = '" & KeyValue & "'"
  27.       Case Else
  28.          MsgBox "ERROR: Invalid key field data type!"
  29.          Exit Function
  30.    End Select
  31.  
  32.    ' Loop backward, counting the lines.
  33.    Do Until rs.BOF
  34.       CountLines = CountLines + 1
  35.       rs.MovePrevious
  36.    Loop
  37.  
  38. Bye_GetLineNumber:
  39.          ' Return the result.
  40.          GetLineNumber = CountLines
  41.  
  42.          Exit Function
  43.  
  44. Err_GetLineNumber:
  45.          CountLines = 0
  46.          Resume Bye_GetLineNumber
  47.  
  48. 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
Oct 5 '07 #5

missinglinq
Expert 2.5K+
P: 3,532
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)>
Oct 5 '07 #6

P: 10
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.
Oct 5 '07 #7

nico5038
Expert 2.5K+
P: 3,072
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)
Oct 5 '07 #8

Scott Price
Expert 100+
P: 1,384
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
Oct 5 '07 #9

Scott Price
Expert 100+
P: 1,384
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
Oct 5 '07 #10

P: 10
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.
Oct 5 '07 #11

P: 10
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
Oct 5 '07 #12

nico5038
Expert 2.5K+
P: 3,072
Hmm, when you want to solve this without code you could use a query with the DCOUNT() function like:
Expand|Select|Wrap|Line Numbers
  1. select Dcount("ID","query_of_subform","POnumber=" & [POnumber] & " AND [ID} <=" & [ID]) as LineCountPO, POnumber, ID, .... from tblPurchaseOrders;
  2.  
This will create a linenumber in the query and you can place the [LineCountPO] "straight away" in your subform.

Nic;o)
Oct 5 '07 #13

missinglinq
Expert 2.5K+
P: 3,532
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)>
Oct 5 '07 #14

P: 10
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
Oct 8 '07 #15

P: 10
Hmm, when you want to solve this without code you could use a query with the DCOUNT() function like:
Expand|Select|Wrap|Line Numbers
  1. select Dcount("ID","query_of_subform","POnumber=" & [POnumber] & " AND [ID} <=" & [ID]) as LineCountPO, POnumber, ID, .... from tblPurchaseOrders;
  2.  
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
Oct 8 '07 #16

missinglinq
Expert 2.5K+
P: 3,532
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)>
Oct 8 '07 #17

P: 10
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
Oct 8 '07 #18

missinglinq
Expert 2.5K+
P: 3,532
Glad we finally figured it out!

Linq ;0)>
Oct 8 '07 #19

nico5038
Expert 2.5K+
P: 3,072
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)
Oct 8 '07 #20

missinglinq
Expert 2.5K+
P: 3,532
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)>
Oct 9 '07 #21

P: 1
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(*))
Mar 19 '08 #22

Scott Price
Expert 100+
P: 1,384
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
Mar 19 '08 #23

Post your reply

Sign in to post your reply or Sign up for a free account.