Using Variable problem

[Ipswmaniac]

I have been trying to workout how to use a variable "Value" in the codes below but everything ive tried returns syntax error can someone show me correct syntax please

Dim Value
Value = "Weapons"
Me.ValueList.RowSource = "SELECT tblValue.Name FROM tblValue;
ValueLabel.Caption = "Att"

 

[pbaldy]

I don't see you trying to use it anywhere, unless it's buried in the other names. If so, which instances of the word Value appearing buried in other words are intended to be the variable?

 

[Ipswmaniac]

I don't see you trying to use it anywhere, unless it's buried in the other names. If so, which instances of the word Value appearing buried in other words are intended to be the variable?

Value is the variable and all instances 4 instances of it
Dim Value
Value = "Weapons"
Me.ValueList.RowSource = "SELECT tblValue.Name FROM tblValue;
ValueLabel.Caption = "Att"

 

[Ipswmaniac]

Hi have tryed googling for answers and also looked for related topics on forum and this best ive come up with

Dim mystr, myvar
myvar = "Wep"
mystr = [myvar] & "No1Label"
Debug.Print mystr
and that displays correct but when i put it in code like

[myvar] & "No1Label".Caption = "Att"

it dont
ive also tryed these
"mystr".caption
([mystr]).caption
("mystr").caption
but im obviously missing something and dont fully understand how to use variables in this way

i would like to use a variable in place of the red parts of this line
WepNo1Label.Caption = "Att"

im hoping the syntax for this will also work with this line
Me.ValueList.RowSource = "SELECT tblValue.Name FROM tblValue;"

 

[pbaldy]

For form references:

Me(Value & "List").RowSource

For the SQL:

"SELECT tbl" & Value & ".Name FROM tbl" & Value

I would add that having multiple tables and needing to switch them out implies a potential normalization issue.

 

[Ipswmaniac]

Thanks pBaldy i appreciate you taking time to help

I have another question in regards to your potential normalization comment

I have 3 lists i want to populate with records from 3 seperate tables, 1 list for each table (tblWeapons, tblArmour and tblVehicles) i have one query to manipulate all 3 tables and was hoping to use 1 function to load each list from there respective tables with a variable in place of the Weapons,Armour and Vehicles part of the code.
This is Non normalization?

This is my code so far

Private Sub Main()
CurrentDb.Execute "UPDATE tblWeapons SET Include = False"
Dim total As Long
Dim CountListHeight, CountAttack
Do Until rs.EOF
CountListHeight = CountListHeight + 1
total = total + rs("No")
rs.Edit

Select Case total
Case Is < 501
rs("Include") = rs("No")
CountAttack = CountAttack + (rs(sQry) * rs("Include"))
rs.Update
Case 501
rs("Include") = rs("No")
rs.Update
CountAttack = CountAttack + (rs(sQry) * rs("Include"))
Exit Do
Case Is > 501
total = total - 501
rs("Include") = (rs("No") - total)
rs.Update
CountAttack = CountAttack + (rs(sQry) * rs("Include"))
Exit Do
End Select

rs.MoveNext
Loop
Set rs = Nothing
Me.WeaponsList.Height = CountListHeight * 260.8
Me.Text8.value = CountAttack
End Sub

Private Sub ListAttackDesc_Click()

sQry = "Attack"
Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblWeapons ORDER BY Attack DESC")
Main
Me.WeaponsList.RowSource = "SELECT tblWeapons.Name, tblWeapons.Attack, tblWeapons.Defence, tblWeapons.Include FROM tblWeapons WHERE (((tblWeapons.Include)=True)) ORDER BY tblWeapons.Attack DESC;"

WepNo2Label.Caption = "Att"
WepNo2Label.Caption = "Def"
TotalLabel.Caption = "Total Attack"
End Sub

 

[pbaldy]

Well, if the 3 tables contain the same fields, then I would say there should only be one table, with an additional field to denote the type (weapon, armor or vehicle). If they contain different fields due to different information being required for each, then it's probably appropriate that they be different tables.

 

[Ipswmaniac]

Thanks pbaldy the fields are identical in each table and combining them with the extra field to show what type will solve many of my problems

Thanks again i appreciate it

 

[Galaxiom]

Thanks pbaldy the fields are identical in each table and combining them with the extra field to show what type will solve many of my problems

BTW. You might already know this but I thought it worth mentioning just to be sure.

Create a Type table with the types recorded against a numeric field TypeID. Don't store the name of the type in the main table. Store the TypeID number.