Static variable is not incrementing the values

[aman]

Hi guys

I am writing the following code that will create new fields in the table when command button is clicked. and will update those fields with some data. But suppose once the field loc(0) is created then the next time loc(1) should be created but it gives the error
"Field loc(0) already exist in the table"

I think there is some problem with static variable that I have declared.

Private Sub Command20_Click()
Static aa As Integer
s1 = "ALTER TABLE rebookinfo ADD [loc(" & aa & ")] varchar(20),[Dept(" & aa & ")] varchar(20);"
DoCmd.SetWarnings False
DoCmd.RunSQL s1
sSQL = "Update rebookinfo set [loc(" & aa & ")] = '" & Me.[Combo33].Value & "' , [Dept(" & aa & ")]= '" & Me.[Combo35].Value & "' where Barcodevalue = '" & Text25.Value & "'"
DoCmd.SetWarnings False
DoCmd.RunSQL sSQL
aa = aa + 1
end sub

 

[DJkarl]

Static variables and variables in general only retain their values while the code is running. Based on the sub name I'm guessing this is a button click, so as soon as all the code is done for the button click the variable loses it's value. You could store the value in a textbox on the form then increment the value, or you could put the value into a table, property, or global variable as those are not reset when code execution stops.

 

[DJkarl]

Good point Bob, I was focused more on why it was failing and not what is was doing. Adding columns like this is most definitely a design no no.

 

[ChrisO]

Actually Static variables do maintain their value between procedure calls, that’s the reason they are called Static.
They are not Global inasmuch as they will not maintain their value if the Form is closed.

If aa was not being incremented then that line of code was not being executed.

With no error handling and a ‘DoCmd.SetWarnings False’ in place, two of them by the way, then it’s difficult to say why that line would not be incremented but perhaps the method of testing its value was faulty.

 

[ChrisO]

Just for the sake of clarity, adding fields to tables on the fly is not really a sign of a non-normalized database design.

I have never released a remote database where the FE could not add fields to a table if a new version required them. It is no more an issue of non-normalized database design to add fields to a table via code as it is to modify the table manually.

When a new version of a FE is sent out that FE must ensure that any additional requirements for table fields are met.

The problem with the code as posted is that it requires the end user to push a button to add the field. That’s a no no because the end user may not push the button before the FE requires that field. Therefore, the FE should add the field at start-up, so as to protect itself, and not leave it up to the end user who may not push the button at all.

Therefore, I would not call it non-normalized database design but rather FE survival in a new version.

 

[jardiamj]

Hahahahahaha!!... I couldn't avoid laughing about it. Pedantic!, I thought Boblarson was being pedantic. I have done projects where I used temporary dummy tables to build dynamical reports (where the number of columns, and the name of them can change), so I just add or delete the columns as needed to the table, and customize the report programatically to match the columns.
I don't see any problem with that, but what is my opinion worth? I'm not a programmer after all. I just taught myself Ms Access, MySQL, Visual Basic, HTML, CSS, XML, PHP, JavaScript, etc... lol

Just a few days in the forum, but I enjoy it. Cheers!!

 

[RainLover]

<Just a few days in the forum, but I enjoy it. Cheers!!>

Please stick around. I like your humour. Shame Bob doesn't. But basically Bob is one of the nice guys. He must have had a bad day.

 

[jardiamj]

Please stick around.

I will be around, that's for sure!.. Cheers!