tgatif
asked on
SubScript Out of Range
I am trying to update a table named OperationRiskData if a user hit the Save Button on the Form . I have following code in onClick
-------------------------- -------Cod e--------- ---------- ---------- ---------- ------
rivate Function ExecuteSQL() As Boolean
Dim sql As String
sql = "insert into OperationRiskData(Branch Name, Review Date,Branch Code,OpsManager Name,Event Description,Event Classification,Example,Bus iness Lines,Approximate Amount,Action Taken,Action Date)" & _
"values(CmbBranchName, DOB,txtBranchCode,Opsmanag er,CboEven t,CboClass ification, CboExample ,txtbusine ssline,txt amount,txt action,txt date);"
CmbBranchName = Forms![frmBranchInfo].CmbB ranchName
DOB = Forms![frmBranchInfo].DOB
txtBranchCode = Forms![frmBranchInfo].txtB ranchCode
Opsmanager = Forms![frmBranchInfo].Opsm anager
CboEvent = Forms![frmBranchInfo].CboE vent
Cboclassification = Forms![frmBranchInfo].Cboc lassificat ion
CboExample = Forms![frmBranchInfo].CboE xample
txtbusinessline = Forms![frmBranchInfo].txtb usinesslin e
txtamount = Forms![frmBranchInfo].txta mount
txtaction = Forms![frmBranchInfo].txta ction
txtdate = Forms![frmBranchInfo].txtd ate
DoCmd.RunSQL sql
End Function
-------------------------- -End Code---------------------- ----
--------Error Message that i am getting
The Expression Onclick you entered as the event property setting produced the following error ;SubScript Out of Range.
*The Expression may not result the name of a macro or event
*There may have been an error evaluating the function or macro
--------------------------
rivate Function ExecuteSQL() As Boolean
Dim sql As String
sql = "insert into OperationRiskData(Branch Name, Review Date,Branch Code,OpsManager Name,Event Description,Event Classification,Example,Bus
"values(CmbBranchName, DOB,txtBranchCode,Opsmanag
CmbBranchName = Forms![frmBranchInfo].CmbB
DOB = Forms![frmBranchInfo].DOB
txtBranchCode = Forms![frmBranchInfo].txtB
Opsmanager = Forms![frmBranchInfo].Opsm
CboEvent = Forms![frmBranchInfo].CboE
Cboclassification = Forms![frmBranchInfo].Cboc
CboExample = Forms![frmBranchInfo].CboE
txtbusinessline = Forms![frmBranchInfo].txtb
txtamount = Forms![frmBranchInfo].txta
txtaction = Forms![frmBranchInfo].txta
txtdate = Forms![frmBranchInfo].txtd
DoCmd.RunSQL sql
End Function
--------------------------
--------Error Message that i am getting
The Expression Onclick you entered as the event property setting produced the following error ;SubScript Out of Range.
*The Expression may not result the name of a macro or event
*There may have been an error evaluating the function or macro
ASKER
Can u explain to me the syntax of values, i am just struggling with access
String must be wrapped in quotes:
'some string'
dates in hash marks (and US-formatted as shown):
#3/31/2007#
decimal numbers with dot as decimal separator:
456.78
- use Str() for this: strDecimal = Str(yourdecimalvalue)
and integer values 'as is':
1234
/gustav
'some string'
dates in hash marks (and US-formatted as shown):
#3/31/2007#
decimal numbers with dot as decimal separator:
456.78
- use Str() for this: strDecimal = Str(yourdecimalvalue)
and integer values 'as is':
1234
/gustav
ASKER
It is still giving me error "Expected end of statement" here is the SQL . The error occurs at the value and specifically at the date formate ( "m\/d\/yyyy).
Is it part of syntax to have backward and forward slash in the date format or is it typo........
here is Sql
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- -
sql = "insert into OperationRiskData(Branch Name, Review Date,Branch Code,OpsManager Name,Event Description,Event Classification,Example,Bus iness Lines,Approximate Amount,Action Taken,Action Date)" & _
"values('" & CmbBranchName & "', # & Format(DOB, "m\/d\/yyyy") & "#, '" & txtBranchCode & "', '" & Opsmanager & "','" & CboEvent & "','" & Cboclassification & "'," & CboExample & "'," & txtbusinessline & "'," & txtamount & "'," & txtaction & "',# & Format(txtdate, "m/d\/yyyy") & "#)"
Is it part of syntax to have backward and forward slash in the date format or is it typo........
here is Sql
--------------------------
sql = "insert into OperationRiskData(Branch Name, Review Date,Branch Code,OpsManager Name,Event Description,Event Classification,Example,Bus
"values('" & CmbBranchName & "', # & Format(DOB, "m\/d\/yyyy") & "#, '" & txtBranchCode & "', '" & Opsmanager & "','" & CboEvent & "','" & Cboclassification & "'," & CboExample & "'," & txtbusinessline & "'," & txtamount & "'," & txtaction & "',# & Format(txtdate, "m/d\/yyyy") & "#)"
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
One more thing if i have a combo box say Branch Names and a text Box on a form, what i want is when i select the option in combo box the corresponding values in the text box will appear and is saved in the table . what i did is i have in control Source i have =CmbBranchName.Column(0)
CmbBranchName is branch name combo .
Problem is the branch code value do not go to corresponding table all i have is 0 in the branch code Column .
is there a utilityi can upload my database and u can take a look,,,,,,,just thinking
CmbBranchName is branch name combo .
Problem is the branch code value do not go to corresponding table all i have is 0 in the branch code Column .
is there a utilityi can upload my database and u can take a look,,,,,,,just thinking
If the textbox is bound to CmbBranchName:
=CmbBranchName.Column(0)
it is not bound to the table.
You may use the AfterUpdate event of the combobox to set the field of the table:
Me!NameOfFieldInTable = Me!CmbBranchName.Column(0)
/gustav
=CmbBranchName.Column(0)
it is not bound to the table.
You may use the AfterUpdate event of the combobox to set the field of the table:
Me!NameOfFieldInTable = Me!CmbBranchName.Column(0)
/gustav
CmbBranchName = Forms![frmBranchInfo].CmbB
DOB = Forms![frmBranchInfo].DOB
txtBranchCode = Forms![frmBranchInfo].txtB
Opsmanager = Forms![frmBranchInfo].Opsm
CboEvent = Forms![frmBranchInfo].CboE
Cboclassification = Forms![frmBranchInfo].Cboc
CboExample = Forms![frmBranchInfo].CboE
txtbusinessline = Forms![frmBranchInfo].txtb
txtamount = Forms![frmBranchInfo].txta
txtaction = Forms![frmBranchInfo].txta
txtdate = Forms![frmBranchInfo].txtd
sql = "insert into OperationRiskData(Branch Name, Review Date,Branch Code,OpsManager Name,Event Description,Event Classification,Example,Bus
"values('" & CmbBranchName & "', # & Format(DOB, "m\/d\/yyyy") & "#, '" & txtBranchCode & "', '" & ... etc. etc.
DoCmd.RunSQL sql
/gustav