Combo Box 1

[drek01]

i donot know whether i should be posting this here.

i have form( its VBA) which displays different field froma table.In every field ( almost all) there are combo box, which dispalys different grading( variables). field in combo box are populated from a field of relevant tables. when i change the variable in combo box for that row, i want it to give option if i wanna change the varaible for every row, and changes will be applied if i want to, else just for that row.

wich i could upload the form.

 

[drek01]

actually it gives messagebox with above posted message on it. and when you click ok it doesnot points to the code like other error message does.

But when you compile project it gives same error and points to
"Private Sub InternalTransactionGrade_Change(sValue As String, sBorrowerName As String, sExamName As String, dAsOfDate As Date)"
this line.

i ran this code in subform where the combo box is, as i mentioned in my post b4.

Private Sub InternalTransactionGrade_AfterUpdate(sValue As String, sBorrowerName As String, sExamName As String, dAsOfDate As Date)
Dim strSql As String
Dim db As ADODB.Connection 'Assuming you are using ado
Set db = CurrentDb.Connection 'I think that is right

strSql = "UPDATE tblMaster SET tblMaster.Internaltransactiongrade = '" & sValue & "' WHERE (tblmaster.borrowername = '" & sBorrowerName & "') and (tblmaster.orgunit = '" & sExamName & "') and (tblmaster.readlistflag = 1) and ((tblmaster.loans+tblmaster.commitments+tblmaster.lcs+tblmaster.tradefinance)>0) and tblmaster.yymmdd = '" & dAsOfDate & "'"

MsgBox strSql
Me.Form.Requery
Exit Sub
Error message

End Sub

 

[CaptainD]

If you set your break point on "strSql ="

<---Break Point--strSql = "UPDATE tblMaster SET tblMaster.Internaltransactiongrade = '" & sValue & "' WHERE (tblmaster.borrowername = '" & sBorrowerName & "') and (tblmaster.orgunit = '" & sExamName & "') and (tblmaster.readlistflag = 1) and ((tblmaster.loans+tblmaster.commitments+tblmaster.lcs+tblmaster.tradefinance)>0) and tblmaster.yymmdd = '" & dAsOfDate & "'"

and step through the code using "F8", when you click "Ok" it will drop you to the next line. If you just hit the "Run" button, it will "Run" the rest of the program.

 

[CaptainD]

Correct that

I re-looked and you have not changed the code as suggested.

Set db = CurrentDb.Connection 'I think that is right

Change Set db = CurrentDb.Connection to Set db = CurrentDB

Plus, you have not added eror trapping to each sub

Private Sub InternalTransactionGrade_AfterUpdate(sValue As String, sBorrowerName As String, sExamName As String, dAsOfDate As Date)

On Error Goto ErrorHandler
Dim strSql As String
Dim db As ADODB.Connection 'Assuming you are using ado
Set db = CurrentDb

strSql = "UPDATE tblMaster SET tblMaster.Internaltransactiongrade = '" & sValue & "' WHERE (tblmaster.borrowername = '" & sBorrowerName & "') and (tblmaster.orgunit = '" & sExamName & "') and (tblmaster.readlistflag = 1) and ((tblmaster.loans+tblmaster.commitments+tblmaster.lcs+tblmaster.tradefinance)>0) and tblmaster.yymmdd = '" & dAsOfDate & "'"

MsgBox strSql
Me.Form.Requery
Exit Sub
ErrHandler:

  MsgBox "Error on update. Error # " & Err.Number & ", " & Err.Description, VBOkonly, "Error"
End Sub

 

[drek01]

it still gives same error and points at


"Private Sub InternalTransactionGrade_AfterUpdate(sValue As String, sBorrowerName As String, sExamName As String, dAsOfDate As Date)
"

 

[CaptainD]

I did not see that on your last 2 post with this event
. You took an "Event" for the textbox and added values from the Sub routine I posted earlier. That is the cause of that error.

Private Sub InternalTransactionGrade_AfterUpdate(sValue As String, sBorrowerName As String, sExamName As String, dAsOfDate As Date)

You have no means to pass values to that event so it throws the error.

This was a sub routine that you pass values through when you call it.

Private Sub UpdateValues(sValue As String, sBorrowerName As String, sExamName As String, dAsOfDate As Date)
On Error Goto ErrHandler

Dim strSql As String
Dim db As ADODB.Connection 'Assuming you are using ado
Set db = CurrentDb 'I think that is right

strSql = "UPDATE tblMaster SET tblMaster.Internaltransactiongrade = '" & sValue & "' WHERE (tblmaster.borrowername = '" & sBorrowerName & "') and (tblmaster.orgunit = '" & sExamName & "') and (tblmaster.readlistflag = 1) and ((tblmaster.loans+tblmaster.commitments+tblmaster.lcs+tblmaster.tradefinance)>0) and tblmaster.yymmdd = '" & dAsOfDate & "'"

'db.Execute strSql


MsgBox strSql

Me.Form.Requery
Exit Sub
ErrHandler:
MsgBox "Error updating tblMaster. Error # " & Err.Number & ", " & Err.Description, VbOkonly, "Error"
End Sub

Instead of drawing this on and on, I think you need to do some homeowrk on VBA.

A search for "VBA Tutorials came up with these.

http://www.functionx.com/vbaccess/

http://www.everythingaccess.com/tutorials.asp

http://www.fontstuff.com/downloads/index.htm

 

[drek01]

thanks for the tutorial, i really need to take a look at that once i am done with this. seriously, i know where i stand. appreciate it.

But the code you posted above doesnot do anything, i mean, well it doesnot changes anything.
when i pass this on my subform to updates as you have posted above.
no difference,

 

[CaptainD]

But the code you posted above doesnot do anything, i mean, well it doesnot changes anything.
when i pass this on my subform to updates as you have posted above.
no difference,

It will not work as posted. As I have suggested, you need to modify it to get the values you want it to have and then call "Sub UpdateValues" with those values in the right location.

It appears to me that all you are doing is pasteing code and hoping it will work. A little homework and you would know how to alter it. Here is a quick sample

Create a form with a command button and two test boxes.
name the text boxes the same as I have them

Command1, Text2, Text4

Paste the code and step through it.

Private Sub Command1_Click()
On Error GoTo Errhandler

Dim iTestNumber As Integer
[COLOR=green]'I have two text boxes. (Text2 and Text4)
'This will get the value and place it in the variable
'If you enter an Alpha character
'It will throw the error because the variable is an Integer

'In access you have to set focus to the textbox first[/color]
Text2.SetFocus
iTestNumber = CInt(Text2.Text)
[COLOR=green]'This will show a message box with your numeric entry.[/color]

MsgBox "The Number you entered in the text box is: " & iTestNumber

[COLOR=green]'Now lets call a sub to do math on the number you entered.[/color]
Call MultiplyValue(iTestNumber)

Exit Sub
Errhandler:
    MsgBox "Error getting your value. Error # " & Err.Number & ", " & Err.Description, vbOKOnly, "Error"
End Sub

Private Sub MultiplyValue(iNumberToMultiply As Integer)
Dim iAnotherNumber As Integer
[COLOR=green]'Lets multiply and show it in Text4[/color]
Text4.SetFocus
Text4.Text = iNumberToMultiply * 3

[COLOR=green]'Now we'll call a "Function" to show how to pass and retrieve values
'Notice where the msgbox is called from.
'The function takes the value now in Text4 and runs it through via iNewNumberToPass[/color]
iAnotherNumber = ShowAnotherNumber(CInt(Text4.Text))
MsgBox "The function value is " & iAnotherNumber


End Sub

Private Function ShowAnotherNumber(iNewNumberPassed As Integer) As String

    ShowAnotherNumber = iNewNumberPassed * 4

End Function