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.

 

[CautionMP]

drek01,
If I follow your question, if a user adds an item that is not currently in the list you want to ask if the item should be permanently added to the list?

Two options:[ol][li]Make sure the ComboBox accepts not in list entries then use the [tt]On Not In List[/tt] event to promt the user to add the item. You could then use [tt]DoCmd.RunSQL[/tt] or such to add the item to the relevent table.[/li][li]Set the [tt]RowSource[/tt] property of the ComboBox to lookup the existing values in the main table (this is slower but eliminates the need for a lookup table).[/li][/ol]

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[drek01]

thanks CMP.
i should had made it clear in first post.
i already have list in ombo box, user cannot add anything out from that list. the row source is populated by this query."select rating from tblTransactionRatings order by OrderNumber"
and the user can change the grade for that row. but sometime they have to apply same grade for all different rows. so i want to give them an option if they want to aply chages for all the rows for that field. if you follow me.
the combo list box is in every row.

Any help is appreciated.
Thanks
Derek

 

[drek01]

Anyone

 

[CaptainD]

You could use an option button or event to trigger a message box to determine if they want to set all of your comboboxes to that selection. If so, cycle through the controls to get the comboboxes name. Then cycle through each value in the combobox to find a match and set the index.

This should give you an idea on how to cycle through the combo boxes

Private Sub Command0_Click()
Dim ctrl As Control

For Each ctrl In Controls
    If TypeOf ctrl Is ComboBox Then
        MsgBox ctrl.Name
    End If
Next ctrl

End Sub

Then, if the type is a combobox do a for i = 0 to combobox.listcount - 1, match the the values to set the listindex when they match.

That should get you started.

 

[CautionMP]

if you follow me. the combo list box is in every row

Sounds like a continuous form?

CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[drek01]

If so, cycle through the controls to get the comboboxes name. Then cycle through each value in the combobox to find a match and set the index."

its same combo box in all the rows of that field. its just that every row has combo box ( in that field) for an option to change the grade for every row.
so,
that means i just have to display message, and if user click yes then loop it through the rows and change the selected grade.

please refresh me here.Appreciate it, CaptainD.

 

[CaptainD]

Are you talking about an Excel spreadsheet that has a column that when clicked on has a combobox that allows you to change a setting that then changes the values in the row that was selected?

or

Did you build a form in MS Access that has a fixed number of rows that you built?

Need to know what program you are working with as the VBA can be different.

Also, you mentioned a message box. Can you explain that a little more on what you want?

 

[drek01]

i have form in MS Access. when i load a form it brings data to the field from a table of my database which in in Sql server. Number of rows are not fixed, every different category( form is loaded based on different name of company) has different number of rows. so i want the user to give option when they change the grade for one row, if they want to change for all the rows, and if selected yes it will change the grade for all the rows.

i really need serious help on this one. as i am very naive to VBA.

Thanks CAPtain D

 

[CautionMP]

drek01,
Continuous Form?

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

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT+08:00) Singapore

 

[drek01]

it gave me error, when i apply changes to all, it only changes that row and the row after that. not the top and rest of the rows. and it gave me error
" run time error- 3021"
" Either BOF or EOF is true, or the current record has been deleted. Request opertion requires a current record"

and after you made change, you cannot change the grade again.

help me out with this.

 

[CaptainD]

If you are using a continuous for as CautionMP (CMP) has mentioned.
(Click on the form and check it’s properties under “Default View” it will have 5 choices, one is “Continuous Form”) then you need to update that data field for all the records that you see at that time. You can use CMP’s method or look into an update query and then refresh the forms data. Just make sure you limit it to the records you want updated.

Can you confirm if this is a continuous form and can you show us the code that populates the form?

 

[drek01]

i quite didnot follow you. please bear with me. i donot know how to to view click continuous form, if you could explain me. and the combobox is populated by query
"select grading from tblTransactionRatings order by OrderNumber"

 

[drek01]

it is continuous form.

 

[drek01]

this is my update statement for that form.:


Private Sub Form_AfterUpdate()
On Error GoTo Err_Form_AfterUpdate

Call activitylog("Asset Quality Review", "General info./Exposure")

Exit_Form_AfterUpdate:
Exit Sub

Err_Form_AfterUpdate:
MsgBox Err.Description, vbCritical
Resume Exit_Form_AfterUpdate

End Sub


RECORD SOurce for form is store procedure which is as follows.

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



// also forgot to metion in the beginning these 4 columns are displayed inside four combo boxex in a single rows. same function( ways to change ) should be applied to other 3 combo boxes as well.
Wish i could post the screen shot of the form. so it would be more clear. hope you got the point.
Any help would be appreciated.

 

[drek01]

also forgot to metion in the beginning these 4 columns are displayed inside four combo boxex in a single rows. those are.
tblMaster.InternalTransactionGrade, tblMaster.CeaTransactionRating, tblMaster.SystemGuarantyFlag,
tblMaster.SecurityFlag

combo box are populated from relevant fields from tblmaster by simple query.

 

[CaptainD]

I've never played with a continuous form other then a simple test I did to look into your problem. With that said,

How are you updating your records?

You are populating your form from a stored procedure which indicates to me you should also have an insert and an update procedure, sql statement or something, otherwise, you're just changing the combobox value and the next time you reload the form the old data is still there. Is that correct or am I missing something?

 

[drek01]

when i change the value in combo box and next time i reload the form the data still remains there, and thats what it does and thats what i want.
so alli need ti do is make it easier for user instead of changing all the grades for exam i can allow them to change one top row so it will cange all the rows and so for other combo boxes on next xolumn as well.
if you follow me.
'll appreciate it, man

 

[CaptainD]

Well, I played around again, other then I did not use a stored procedure, and I'm not getting the change like you are. Checking the form name and combobox name on the lost fucus event, all of the form "replications" for the continuous form, have the same name and same event. Meaning that each combobox fires the lost focus event. That tells me that if you want the change in the top combobox to fire a messagebox asking to change all the values, it's going to do that for each combobox. other then the fact that they may not touch the other comboboxes, that could be a headache for the user.

That aside, I would use an update statment to change the values and then requery the form.

This is not tested but should give you an idea.

Private Sub UpdateValues( sValue as String, sBorrowerName as String, sExamName as String, dAsOfDate as DATE)
'error haandling here
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.YourField = '" & 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

Exit Sub
Error message
End Sub

Once something like this runs, requery the form.

 

[drek01]

Sorry CaptainD, i didnot get this. please bear with me here, since i am very new to VBA and all of these.
where do i implement those code on event afterupdate of form or of combobox.

thankyou