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]

if i do not run this how do i update the main form.



i only want to change the subform. i donot want to the change of grade ( internal trasactiongrade, or combobox) of subform to be applied on the main form which has the same name of combo box and it(combo box ) takes the record from same table. wheare as the subform has the borrowers different credit type which has different grade. which is what i want to be changed. i do not want this grade change update the main grade of borower.

How do i post the attchment(screenshot) of my form here.

 

[CaptainD]

You show two sub forms

Me.fsubcountryrisk.Form.RecordSource = "select borrowercountryrisk, borrowerbtmcountryrat, borrowermoodyscountryrat, " & _
" borrowerspcountryrat from tblborrowerinformation where borrowername ='" & Me!cmbborrower & "' AND tblBorrowerInformation.ExamName='" & gstrExamName & "' AND tblBorrowerInformation.ExamAsOfDate='" & gstrExamAsofDate & "'"


Me.fsubPoliciesExcep.Form.RecordSource = "Spr_CrmExcepBorrower"

Since Me.fsubcountryrisk.Form has parameters, I assume that is the table you want to update. In that select statement I see three "rate" fields. Correct?
1. borrowerbtmcountryrat
2. borrowermoodyscountryrat
3. borrowerspcountryrat

If so, your update statment to update borrowerbtmcountryrat would be:

"UPDATE tblborrowerinformation SET borrowerbtmcountryrat = '" &[COLOR=blue]ComboBox.value[/color]"' where borrowername ='" & Me!cmbborrower & "' AND tblBorrowerInformation.ExamName='" & gstrExamName & "' AND tblBorrowerInformation.ExamAsOfDate='" & gstrExamAsofDate

Now, if my assumptions are correct, then you need to figure out from where you want to run the update statement.

 

[drek01]

no actually the grading i want to change is in other subform, which is fsubcreditmaster, ia sof that i have posted store peocedure and all the codings.
this subform has "link child" and "Link Master" field as borrowerName.
this is the form which has 4 combo boxes in a row( 4 different one) and records it take has many rows. when i change the combo box values,and refresh it, or close it, it saves the changed value. but now i want user be able to make changes to all the columns(that fields) of that combo box applied, if they want to.
same thing to be applied to other 3combo boxes as well.

Here is the store procedure of that that subform.

ALTER Procedure Spr_fsubCreditMaster
(@ParExamName varchar(50),@ParAsofDate smalldatetime,@ParBorrowerName varchar(255))
as
Begin
SELECT tblMaster.InstrumentID, tblMaster.TypeOfCredit,
(tblMaster.ApprovedCrLine) / 1000 as ApprovedCrLine,(tblMaster.TotalExposure) / 1000 as TotalExposure,
(tblMaster.Loans) / 1000 as Loans, (tblMaster.TradeFinance) / 1000 as TradeFinance,
(tblMaster.Commitments) / 1000 as Commitments,(tblMaster.LCs) / 1000 as LCs,
(tblmaster.participationsold) / 1000 as participationsold,tblMaster.BookingOffice,
tblMaster.InternalTransactionGrade, tblMaster.CeaTransactionRating, tblMaster.SystemGuarantyFlag, tblMaster.SecurityFlag
FROM tblMaster
where (tblmaster.borrowername = @ParBorrowerName) and (tblmaster.orgunit = @ParExamName) and (tblmaster.readlistflag = 1) and ((tblmaster.loans+tblmaster.commitments+tblmaster.lcs+tblmaster.tradefinance)>0) and tblmaster.yymmdd =@ParAsofDate
End



the cobo boxes are, iternaltrasaction grade,Ceatransactionrating,systemGuarantyFlag, and SecurityFlag.

 

[CaptainD]

Ok, you know the table, you know the fields and you know the parameters to filter the records on.

Re-write the update statment to fit that table, get the combobox values you want and using tha parameters you have, run the update statement.

 

[drek01]

yes, i get that but how should i change multiple rows with same records(from combo box) if i want to. Here was the kind of similar codes posted earlier by CautionMP, however it gave me error.

Private Sub cmbGrade_Change()
Dim varBookmark As Variant, varValue As Variant
If MsgBox("Apply to all", vbYesNo) = vbYes Then
varBookmark = Me.CurrentRecord
varValue = Me.cmbGrade
Do
Me.cmbGrade = varValue
Me.Recordset.MoveNext
Loop Until Me.Recordset.EOF
Me.Recordset.MoveFirst
Do While Me.CurrentRecord <> varBookmark
Me.cmbGrade = varValue
Me.Recordset.MoveNext
Loop
End If
End Sub

 

[CaptainD]

You do that by changing the field values in the database and refreshing the form. That is what the update statement is for. You run the update and refresh the form.

That is what CautionMP's technique is supposed to do. It steps through each record and makes the change but being a bound continuous form, it does not work. (Or at least I could not get it to work on my test sample.)

Why don't you want to run the update statement?

 

[drek01]

i did run an update statement in subform, where the record has to be changed. here is what the update statement is :

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

once you change 1st combo box on 1st row, and then it gives error saying "procedure declaration doesnot match desciption of an event or procedure having same name"

 

[drek01]

am i supposed to run that statement in main form or subform. since main form also has same combo boxes, which represents the grade for borrower, while subform represents the type of borrows that borrower has.

i don't think i should be running that update statement on main form.

here is the code that updates the main forms same combo box grade.

Private Sub InternalBorroweGrade_AfterUpdate()
On Error GoTo Err_InternalBorroweGrade_AfterUpdate

txtExaminerId.Value = gstrUserId
txtUpdated.Value = Now



Exit_InternalBorroweGrade_AfterUpdate:
Exit Sub

Err_InternalBorroweGrade_AfterUpdate:
MsgBox Err.Number & " " & Err.Description, vbCritical, "CIMS"
Resume Exit_InternalBorroweGrade_AfterUpdate

End Sub

 

[CaptainD]

As I suggested before, an easy way to test where you want to run the statement from is to place a message box under each event that tells you where it came from and see what works best for you.

Example

Msgbox "Update statement running from Main form combobox event", vbokonly, "Ran from main form"

Once you find what you like, create the SQL statement and pop that up in the message box to review that it has the information you want. Once you are happy with that, run the update statement on a test database or after backing up your database and see if it works the way you wanted it to.

What I've suggested is not hard to do. If you don't want to run it on the current database, create a small test database and run it there till your comfortable with what you are doing.

 

[drek01]

i know what you mean but i donot know how to run this updated statment with your message box,


CODE
Msgbox "Update statement running from Main form combobox event", vbokonly, "Ran from main form"

 

[CaptainD]

The message box is to test locations so that when you change a combobox value or add a command button to run the statement, the message box pops up.

That way you can see if that is from where you want to execute your code.

Then, once that is determined, you create the update statement that incorporates your values from the comboboxes or from where ever you filter your data and run that through the message box to look over the statement to check for errors, missing spaces, commas etc.

Dim strSql as string

strSql = "Update statment goes here"

msgbox strSql

 

[drek01]

i ran the update statement on my subform 'creditmaster' on combobox onchange event, it gave same message as b4. i am not getting this.
in main form there is already afterupdate event for that combo boxes.

so on ran that on subform.


i am not getting this, its just not hitting me.

 

[stevexff]

CaptainD, a star doesn't really do this thread justice, but it's all I can give. You should be canonised at least...

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[CaptainD]

stevexff, thanks for the star.

drek01,

1. Did you place the message boxes as i suggested?
2. Can you post your update statement?

 

[drek01]

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


i put same code to update tblmaster having same borrower name, exam name, date with same values it gives me same error.

and i still did not get it what message do u want me to put on msgbox,
i know you must have been upset.,

 

[CaptainD]

i know you must have been upset.,

Frustrated, Yes, Upset, No...

The idaea for the message box(s) is first, to help figure out from where and how you want to run the update. Remember, each combobox in the continuous form will fire the event, I don't think that would be the way to do it, but that dicision is up to you. The message boxes poping up should help you decide.

Second, placing your sql statement in the message box lets you see the sql statement as it will be passed to the server. You can make sure the code is correct, the appostraphies are correct and the values you want to fitler by are correct.

Once that is done, it should run.

Is this the error you are refering to?

" run time error- 3021"
" Either BOF or EOF is true, or the current record has been deleted. Request opertion requires a current record"

If that is so, then you are not returning anything. Either the connection to the database is not there or your SQL statement is not correct.

I suspect the "Set db = currentdb.connection" is not right.

Try changing Set db = CurrentDb.Connection to Set db = Currentdb()

and Step through the code. so you can see what is going on when you run the program. I explained how earlier.

 

[drek01]

when i run the above code from my subform where the combo boxes are, from on change event of the conbo box.
when i change the value in combobox it gives me message box saying
"
the expression on change you entered as event property setting produced the following errorrocedure declaration doesnot match the description or procedure having same name."

 

[CaptainD]

Did you step through your code to find what was causing the error?

(What it will do it hit a line then drop the the error trapping code. Like I mentioned. it is probably the Set db code.)

AND

Did you change the Set db = currentdb code the way I suggested?

 

[drek01]

yes i did, still same error

 

[CaptainD]

When you stepped through the code, you should have seen the line that threw the error, can you post the line that caused the error?