Combobox (1 Viewer)

[rd_sheth]

Was actually trying some database for my company.

I have Form in which have below feilds:

Field: Rate
Field: ConvertRate
than have 2 Combobox
1st Combobox: Rate per Unit
2nd Combobox: Convert Rate Unit

Now i want to compare 2 Combobox and than get value of ConvertRate

Example

If ComboBox Rate Per Unit value selected kg
and ComboBox Convert Rate Unit value Selected kg too then Field ConvertRate should be equal to Field Rate
but if
ComboBox Convert Rate Unit Value selected m2 than the Feild ConvertRate should calculate (Rate /1000) * gsm (GSM is also Field).
and if ComboBox Convert Rate is other than values mentioned above than the Field ConvertRate should Calculate (Rate / 1000)

Wrote code as under:

Private Sub convert_Unit_Rate_AfterUpdate()
If [convert_Unit_Rate] = [Rate_per_Unit] Then
[ConvertRate] = [Rate]
ElseIf [convert_Unit_Rate] = "m2" And [Rate_per_Unit] = "kg" Then
[ConvertRate] = [Rate] / 1000 * [GSM]
Else
[ConvertRate] = [Rate] / 1000
End If
End Sub

in above code ElseIf part is not working... No Error... kindly suggest....

 

[GaP42]

Deciphering what you want is difficult - confused labels for fields and controls - hence no replies I think.
Your code for the after update event is for the control: convert_Unit_Rate - this is not the name of the control in the description of the problem.

Observation: the only time when the code executes is when the cbo convert_Unit_Rate is changed. A change to the other cbo will not trigger this routine.

When an update is performed, the value is written to the table:
Not knowing exactly the row source for both cbos and any bound field (Are the combos driven from value lists or queries?) it is difficult to determine the correct routine. Are the fields you mention in the one table (suggesting updates from a value list)

Fields			Calculated value on form or query
Rate	ConvertRate	GSM	(RESULT - aka ConvertRate)
kg val	kg	-	kg
kg val	m2	gsm - val	kg/1000 * GSM
"other - val	-	-	rate/1000

And you want a calculation performed based on the combination, however you are writing this to ConvertRate. Calculated values should not be stored. Untested - depends upon your naming/structure

If cboconvert_Unit_Rate = cboRate_per_Unit Then         ' the text kg = kg ??
        [ConvertRate] =me.[Rate]   ' the ConvertRate is a calculated value - cannot be 'kg' - but reads a value from the current record in field Rate
  Else                                                                                ' does not match
        If `(cboconvert_Unit_Rate = "m2" And cboRate_per_Unit = "kg") Then
                [ConvertRate] =me.[Rate] / 1000 * me.[GSM]
          Else
                [ConvertRate] = me.[Rate] / 1000
       End If
End if

 

[Mike Krailo]

It's going to be more helpful if you provide a sample database that can be examined. The thing that doesn't make any sense to me is the fact that you can select kg from both "Rate per Unit" and "Convert Rate Unit". Kilo Grams is a unit of measure, and not a Rate per Unit. So you have obviously miss named your combo boxes or have given us bad sample data. If what is shown in combo 1 and 2 are actual number values, then that makes more sense, but a sample database is easier for us to provide quicker answers.

 

[XPS35]

Crossposted here https://learn.microsoft.com/en-us/a...-written-the-code-but-elseif-statement-is-not

 

[rd_sheth]

It's going to be more helpful if you provide a sample database that can be examined. The thing that doesn't make any sense to me is the fact that you can select kg from both "Rate per Unit" and "Convert Rate Unit". Kilo Grams is a unit of measure, and not a Rate per Unit. So you have obviously miss named your combo boxes or have given us bad sample data. If what is shown in combo 1 and 2 are actual number values, then that makes more sense, but a sample database is easier for us to provide quicker answers.

Mike sending you herewith the Data... thanks

 

[GaP42]

You ha0ve embedded the logic for showing values in the combos concerned as SQL statements in the table. One if these (convert rate unit) refers to a table Unit that does not exist in the sample provided. Hence it does not show values on the form. The use of Lookup fields in your table item definitions is not recommended - Google "The Evils of Lookup Fields in Access Tables"

That issue aside, you do have a number of design problems: the Raw Material table needs to be reviewed to remove the items that are found in the other tables. Use the relationships to drag those values into queries supporting forms and reports as needed - do not copy them to another table - it introduces duplicate values and forces significant problems in maintaining the integrity of your data - you need to understand the concept of database normalisation - fundamental to database design.

There are a number of other problems - use of the field name ID for the Primary Key of every table. Rename these to the name of the thing they identify - eg RateID, RateUnitID, UnitID etc. - these are the names you should use as the item names in the Raw Material table (possibly with "FK" as a suffix). Use of spaces in names and special characters is also discouraged.

 

[GaP42]

The logic for the combos - eg for Convert Unit Rate is not comparing what you expect. eg when "kg" is selected, the VBA for that combo is "2" so it will not match except accidentally to a value held by Rate_Per_Unit. The property you want to compare is the text displayed in the combo.
me.combo.text = "kg" (the default, when the property is not specified is me.combo.value) which as the field is defined as number, and the bound column is the first col value in your SQL - which is ID - returns a number not text.
Fixing up what was described previously will help make your needed changes and logic clearer.

 

[rd_sheth]

The logic for the combos - eg for Convert Unit Rate is not comparing what you expect. eg when "kg" is selected, the VBA for that combo is "2" so it will not match except accidentally to a value held by Rate_Per_Unit. The property you want to compare is the text displayed in the combo.
me.combo.text = "kg" (the default, when the property is not specified is me.combo.value) which as the field is defined as number, and the bound column is the first col value in your SQL - which is ID - returns a number not text.
Fixing up what was described previously will help make your needed changes and logic clearer.

Ok... Thanks so made little changes taking in to consideration what you said and below is the code which is giving me the result i want...


Private Sub convert_Unit_Rate_AfterUpdate()
If Me.convert_Unit_Rate = Me.Rate_per_Unit Then
Me.ConvertRate = Me.Rate

ElseIf Me.convert_Unit_Rate.Text = "m2" Then
Me.ConvertRate = (Me.Rate / 1000) * Me.RM_GSM
Else
Me.ConvertRate = Me.Rate / 1000
End If

End Sub

 

[rd_sheth]

You ha0ve embedded the logic for showing values in the combos concerned as SQL statements in the table. One if these (convert rate unit) refers to a table Unit that does not exist in the sample provided. Hence it does not show values on the form. The use of Lookup fields in your table item definitions is not recommended - Google "The Evils of Lookup Fields in Access Tables"

That issue aside, you do have a number of design problems: the Raw Material table needs to be reviewed to remove the items that are found in the other tables. Use the relationships to drag those values into queries supporting forms and reports as needed - do not copy them to another table - it introduces duplicate values and forces significant problems in maintaining the integrity of your data - you need to understand the concept of database normalisation - fundamental to database design.

There are a number of other problems - use of the field name ID for the Primary Key of every table. Rename these to the name of the thing they identify - eg RateID, RateUnitID, UnitID etc. - these are the names you should use as the item names in the Raw Material table (possibly with "FK" as a suffix). Use of spaces in names and special characters is also discouraged.

Noted your recommendation... will change accordingly....

 

[rd_sheth]

Thanks all... it has helped me a lot and can understand it much better....

 

[GaP42]

Just a further word of warning - from the limited view of what was shown in the Raw Material table, it was possible to select any of the allowed convert Rate Unit values with any allowed Rate values.
That means the if statements need to accommodate each of the possible circumstances. IfElse can be replaced by a Select Case expression - which is generally easier to follow the logic.

in your case consider this as more readable and more easily maintained.

Select CASE me.convert_unit_rate.text
    CASE "kg"
        Select CASE me.rate_per_unit.text
              Case = "kg"    ' they match!
                    me.ConvertRate = me.rate
              Case Else
                    msgbox "Invalid combo"      ' or what should happen here?
        End Select
    CASE "m2"
        me.convertRate = (me.rate / 1000) * Me.RM_GSM
    CASE ELSE
        me.convertRate = Me.Rate/1000
End Select

Good luck

 

[rd_sheth]

Just a further word of warning - from the limited view of what was shown in the Raw Material table, it was possible to select any of the allowed convert Rate Unit values with any allowed Rate values.
That means the if statements need to accommodate each of the possible circumstances. IfElse can be replaced by a Select Case expression - which is generally easier to follow the logic.

in your case consider this as more readable and more easily maintained.

Select CASE me.convert_unit_rate.text
    CASE "kg"
        Select CASE me.rate_per_unit.text
              Case = "kg"    ' they match!
                    me.ConvertRate = me.rate
              Case Else
                    msgbox "Invalid combo"      ' or what should happen here?
        End Select
    CASE "m2"
        me.convertRate = (me.rate / 1000) * Me.RM_GSM
    CASE ELSE
        me.convertRate = Me.Rate/1000
End Select

Good luck

Ok noted... shall try it...

 

[rd_sheth]

Hi,

The case worked perfectly well... thanks...

I have a problem been trying different things but not able to solve it.

I have a Form named "State" - it has 2 Nos. of Record Solid & Liquid.

Also have Main Form "Raw Material" which has a Combo Box getting value Solid & Liquid.

Now i want to create a EDIT Button where i can open Form "State" to a specific record say Liquid.

But it is opening to a specific record.

have written code as under:

Private Sub EDit_Click()
DoCmd.OpenForm "State", , , "S_State = " & Me.RM_State

End Sub

have attached the file for your reference.

Thanks

 

[Mike Krailo]

Now i want to create a EDIT Button where i can open Form "State" to a specific record say Liquid.

But it is opening to a specific record.

You lost me there. You want to open a specific record, but its opening to a specific record?

 

[rd_sheth]

No it is not opening... it gives error or other differnt things i tried it just shows alll the record...

 

[Gasman]

it gives error or other differnt things

That is as much use as a chocolate fireguard.
Please give actual error codes and their descriptions.

 

[Gasman]

You are using table lookups. Not recommended.

You will not find your record as you have the ID in the combo but are looking for the name in State.
You need to lookfor S_ID not S_State with your combo or use the correct column.

Personally I would use the ID, but if you want to use the name

DoCmd.OpenForm "State", , , "S_State = '" & Me.RM_State.Column(1) & "'"

or

DoCmd.OpenForm "State", , , "S_ID = " & Me.RM_State

Your error was Type mIsmatch.
If you had stated that, people could have helped sooner.
You also should have Option Explicit at the top of every module. You need to add it to existing modules, but to have it added automatically for new modules,inthe VBA editor, use Tools/Options/Require Variable Declaration