User Defined Function Returns Ambiguous Name Error

[HotsauceHero]

First, please be a bit patient with me as I am picking Access/VBA back up after not doing it for seven or so years. It's amazing what you can pickup immediately and what has to be drawn from the depths.



That being said here is my issue:

I am trying to change field names in a table, but I don't know if my code is right because I can't get it to run!

Here is the code:



Private Sub Command8_Click()
DoCmd.OpenQuery "qryUsageTotals2", acViewNormal, acReadOnly
DoCmd.DeleteObject acTable, "tblInventory"
DoCmd.Rename "tblInventory", acTable, "tblInventoryTest"
Public Function chgfldnm()


End Function
End Sub




Public Function chgfldnm(Qty As String, QtyTest As String)

Dim db As Database
Dim tbf As TableDef
Dim n As Object

Set db = CurrentDb
Set tbf = TableDefs("tblInventory")
For Each n In tdf.Fields
If n.Name = QtyTest Then n.Name = Qty
Next n

Set tdf = Nothing
Set db = Nothing
End Function

End Function

When I try to run or compile I get:

Compile Error:
Ambiguous name detected:chgfldnm

I have searched the net for four hours trying to find an answer. Please Help!!!!!

[June7]

That usually means duplicate procedure name, either another procedure or a module have same name.

[HotsauceHero]

Nope, that's all the code.

[HotsauceHero]

this is the line the compiler stops at:

Public Function chgfldnm(Qty As String, QtyTest As String)

[June7]

Look at the Sub procedure. The function declaration is duplicated within the Sub. Cannot have a procedure within another procedure. And that's where the duplication is.

[HotsauceHero]

So the function is defined properly, I was trying to CALL the function in the sub. God I've forgotten so much.

[June7]

A function is used to return a value to the calling procedure.
To call a function, must be used in an expression, like:

x = chgfldnm(data1, data2)

Perhaps it should just be declared as a sub, then call the sub:

Call chgfldnm(data1, data2)

Just noticed the End Function line is duplicated at the end of the function.

Why are you programmatically changing field name?

[HotsauceHero]

I was trying to update inventory totals in tblInventory by minusing sold inventory from existing inventory. I bombed on an update query, so I was trying to create a new table and rename it and the fields to the old table name. Dirty, I know.

[June7]

Why write code just to create a table?

The bigger question is: Why are you saving aggregate data? Aggregate data should be calculated when needed. Sum product in, sum product out, calculate the difference. Review: http://allenbrowne.com/AppInventory.html