Problem with form and coding

[Guest]

I have a table named Case with columns CaseID, CaseDate, and Vehicle where
CaseID is the primary key. It is possible to have multiple cases for the same
vehicle as illustrated below:

CaseID | CaseDate | Vehicle
-------------------------------------
Case01 | 01/01/2007 | Vehicle01
Case02 | 02/01/2007 | Vehicle02
Case03 | 03/01/2007 | Vehicle01

I have created a form with a combo box (named VehicleSearch). This combo box
lists the values using the following statement:
SELECT DISTINCT Case.Vehicle FROM [Case] ORDER BY Case.Vehicle;

Upon choosing the Vehicle, the combo will run the following code:
With VehicleSearch
If .ListIndex <> -1 Then
sVehicleNo = .SelText
'Retrieves the value of the bound column which may more may not be
'displayed in the list box
sVehicleNo = .Column(.BoundColumn - 1)
Filter = "VehicleNo=" & sVehicleNo
FilterOn = True
MsgBox "Filter Applied"
End If
End With

This form is supposed to list the records (configured to display as
Continuous Form), based on the value filtered in the combo box.

The problem that I faced is that when I have chosen the Vehicle that I
wanted, there is a pop-up box that will require me to key-in the same Vehicle
again and click on OK. If not, the form will not filter the records based on
the Vehicle.

Is it possible to change it so that I just need to choose from the combo box
and not need to re-key-in the Vehicle again?

Thanks.

Best regards,
Kelvin

 

[storrboy]

Hey Kevin,

Since you only have one column in the combo, it has to be the
BoundColumn. The BoundColumn is the value of the combobox. So I would
think all you need to do is disable the filter if the combo does not
contain a value and set...

Me.Filter = "VehicleNo='" & Me!VehicleSearch & "'"
Me.FilterOn = True

if it does. Unless I'm missing something, your remaining code is
unnecessary.

 

[Guest]

Hi Storrboy,

Thanks for the info. It worked. I have forgotten to mention in my problem
that this form takes the data from 2 tables. One, as I've mentioned is Case
table and the other is CaseDet table. CaseDet table consist of detailed info
of Case and linked by CaseID. The relationship between Case and CaseDet is 1
to many.

The resultant view will be as follows:
CaseID | VehicleNo | CaseDet1 | CaseDet2
--------------------------------------------------------
Case01 | Car1 | abc | Det1b
Case01 | Car1 | xyz | Det2b
Case02 | Car2 | abc | Det3b
Case03 | Car1 | abc | Det4b

I would like to view it after doing some query adjustments to CaseDet2 in a
way that if :

CaseDet1 = ABC, then CaseDet2 goes into Col1
CaseDet1 = XYZ, then CaseDet2 goes into Col2

Is it possible to make it in a way the form shows the following result:

CaseID | VehicleNo | Col1 | Col2

 

[Guest]

Hi Storrboy,

Thanks for the info. It worked. I have forgotten to mention in my problem
that this form takes the data from 2 tables. One, as I've mentioned is Case
table and the other is CaseDet table. CaseDet table consist of detailed info
of Case and linked by CaseID. The relationship between Case and CaseDet is 1
to many.

The resultant view will be as follows:
CaseID | VehicleNo | CaseDet1 | CaseDet2
--------------------------------------------------------
Case01 | Car1 | abc | Det1b
Case01 | Car1 | xyz | Det2b
Case02 | Car2 | abc | Det3b
Case03 | Car1 | abc | Det4b

I would like to view it after doing some query adjustments to CaseDet2 in a
way that if :

CaseDet1 = ABC, then CaseDet2 goes into Col1
CaseDet1 = XYZ, then CaseDet2 goes into Col2

Is it possible to make it in a way the form shows the following result:

CaseID | VehicleNo | Col1 | Col2

 

[storrboy]

I think you might be moving into crosstab query realm with this one.
Sorry, but I don't have much experience with them.

 

[Peter Hibbs]

Kelvin

For displaying the data in a user friendly format you could use a Flex
Grid control but you would need to write a bit of VBA code to do this.
See this site for some examples.

http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='Flex Grid Demo.mdb'

HTH

Peter Hibbs.

 

[Guest]

Dear Peter,

Thanks for your suggestion. But it may take time for me to really how it
works and how I can implement this into my database.

I managed to get the form to view the way I want it but the form keeps
popping up for a parameter value.

CaseID | VehicleNo | Col1 | Col2 | Subtotal
-------------------------------------------------------------
Case01 | Car1 | Det1b | Det2b | Det1b + Det2b
Case02 | Car2 | Det3b | 0.00 | Det3b + 0.00
Case03 | Car1 | Det4b | 0.00 | Det4b + 0.00

using the expression in a query:
sum(iif(CaseDet1 = 'ABC', [CaseDet2], 0.00)) as Col1
sum(iif(CaseDet1 = 'XYZ', [CaseDet2], 0.00)) as Col2
([Col1] + [Col2]) as Subtotal

But it keeps popping a pop-up "Enter Parameter Value" for Col1 and Col2. How
do I prevent the form from popping-up the "Enter Parameter Value" for Col1
and Col2?

Thanks again to all who have tried helping me out.

Best regards,
Kelvin

 

[Peter Hibbs]

Hi Kelvin

I can only suggest you post the SQL for the query along with the field
types, etc and someone may be able to help. What happens if you remove
the ([Col1] + [Col2]) as Subtotal part of the query.

Peter Hibbs.

Dear Peter,

Thanks for your suggestion. But it may take time for me to really how it
works and how I can implement this into my database.

I managed to get the form to view the way I want it but the form keeps
popping up for a parameter value.

CaseID | VehicleNo | Col1 | Col2 | Subtotal
-------------------------------------------------------------
Case01 | Car1 | Det1b | Det2b | Det1b + Det2b
Case02 | Car2 | Det3b | 0.00 | Det3b + 0.00
Case03 | Car1 | Det4b | 0.00 | Det4b + 0.00

using the expression in a query:
sum(iif(CaseDet1 = 'ABC', [CaseDet2], 0.00)) as Col1
sum(iif(CaseDet1 = 'XYZ', [CaseDet2], 0.00)) as Col2
([Col1] + [Col2]) as Subtotal

But it keeps popping a pop-up "Enter Parameter Value" for Col1 and Col2. How
do I prevent the form from popping-up the "Enter Parameter Value" for Col1
and Col2?

Thanks again to all who have tried helping me out.

Best regards,
Kelvin

Kelvin

For displaying the data in a user friendly format you could use a Flex
Grid control but you would need to write a bit of VBA code to do this.
See this site for some examples.

http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='Flex Grid Demo.mdb'

HTH

Peter Hibbs.

 

[AccessVandal via AccessMonster.com]

Hi,

Try "[Col1] + [Col2] as Subtotal". without the curve brackets.

Kelvin Leong wrote:

CaseID | VehicleNo | Col1 | Col2 | Subtotal
-------------------------------------------------------------
Case01 | Car1 | Det1b | Det2b | Det1b + Det2b
Case02 | Car2 | Det3b | 0.00 | Det3b + 0.00
Case03 | Car1 | Det4b | 0.00 | Det4b + 0.00

using the expression in a query:
sum(iif(CaseDet1 = 'ABC', [CaseDet2], 0.00)) as Col1
sum(iif(CaseDet1 = 'XYZ', [CaseDet2], 0.00)) as Col2
([Col1] + [Col2]) as Subtotal

But it keeps popping a pop-up "Enter Parameter Value" for Col1 and Col2. How
do I prevent the form from popping-up the "Enter Parameter Value" for Col1
and Col2?

 

[Douglas J. Steele]

You cannot refer to computed columns in queries: you have to repeat the
calculation.

Try:

sum(iif(CaseDet1 = 'ABC', [CaseDet2], 0.00) + iif(CaseDet1 = 'XYZ',
[CaseDet2], 0.00)) as Subtotal


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Dear Peter,

Thanks for your suggestion. But it may take time for me to really how it
works and how I can implement this into my database.

I managed to get the form to view the way I want it but the form keeps
popping up for a parameter value.

CaseID | VehicleNo | Col1 | Col2 | Subtotal
-------------------------------------------------------------
Case01 | Car1 | Det1b | Det2b | Det1b + Det2b
Case02 | Car2 | Det3b | 0.00 | Det3b + 0.00
Case03 | Car1 | Det4b | 0.00 | Det4b + 0.00

using the expression in a query:
sum(iif(CaseDet1 = 'ABC', [CaseDet2], 0.00)) as Col1
sum(iif(CaseDet1 = 'XYZ', [CaseDet2], 0.00)) as Col2
([Col1] + [Col2]) as Subtotal

But it keeps popping a pop-up "Enter Parameter Value" for Col1 and Col2.
How
do I prevent the form from popping-up the "Enter Parameter Value" for Col1
and Col2?

Thanks again to all who have tried helping me out.

Best regards,
Kelvin

Kelvin

For displaying the data in a user friendly format you could use a Flex
Grid control but you would need to write a bit of VBA code to do this.
See this site for some examples.

http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='Flex Grid Demo.mdb'

HTH

Peter Hibbs.

 

[Guest]

Hi Douglas,

Thanks for the advise. It helped and the form no longer have the pop-ups.

Thanks Peter and AccessVandal for your time to look at my problem as well.

Best regards,
Kelvin

You cannot refer to computed columns in queries: you have to repeat the
calculation.

Try:

sum(iif(CaseDet1 = 'ABC', [CaseDet2], 0.00) + iif(CaseDet1 = 'XYZ',
[CaseDet2], 0.00)) as Subtotal


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Dear Peter,

Thanks for your suggestion. But it may take time for me to really how it
works and how I can implement this into my database.

I managed to get the form to view the way I want it but the form keeps
popping up for a parameter value.

CaseID | VehicleNo | Col1 | Col2 | Subtotal
-------------------------------------------------------------
Case01 | Car1 | Det1b | Det2b | Det1b + Det2b
Case02 | Car2 | Det3b | 0.00 | Det3b + 0.00
Case03 | Car1 | Det4b | 0.00 | Det4b + 0.00

using the expression in a query:
sum(iif(CaseDet1 = 'ABC', [CaseDet2], 0.00)) as Col1
sum(iif(CaseDet1 = 'XYZ', [CaseDet2], 0.00)) as Col2
([Col1] + [Col2]) as Subtotal

But it keeps popping a pop-up "Enter Parameter Value" for Col1 and Col2.
How
do I prevent the form from popping-up the "Enter Parameter Value" for Col1
and Col2?

Thanks again to all who have tried helping me out.

Best regards,
Kelvin

Kelvin

For displaying the data in a user friendly format you could use a Flex
Grid control but you would need to write a bit of VBA code to do this.
See this site for some examples.

http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='Flex Grid Demo.mdb'

HTH

Peter Hibbs.

On Tue, 20 Mar 2007 05:52:00 -0700, Kelvin Leong

Hi Storrboy,

Thanks for the info. It worked. I have forgotten to mention in my
problem
that this form takes the data from 2 tables. One, as I've mentioned is
Case
table and the other is CaseDet table. CaseDet table consist of detailed
info
of Case and linked by CaseID. The relationship between Case and CaseDet
is 1
to many.

The resultant view will be as follows:
CaseID | VehicleNo | CaseDet1 | CaseDet2
--------------------------------------------------------
Case01 | Car1 | abc | Det1b
Case01 | Car1 | xyz | Det2b
Case02 | Car2 | abc | Det3b
Case03 | Car1 | abc | Det4b

I would like to view it after doing some query adjustments to CaseDet2
in a
way that if :

CaseDet1 = ABC, then CaseDet2 goes into Col1
CaseDet1 = XYZ, then CaseDet2 goes into Col2

Is it possible to make it in a way the form shows the following result:

CaseID | VehicleNo | Col1 | Col2
-------------------------------------------------------------
Case01 | Car1 | Det1b | Det2b
Case02 | Car2 | Det3b | -
Case03 | Car1 | Det4b | -



:

Hey Kevin,

Since you only have one column in the combo, it has to be the
BoundColumn. The BoundColumn is the value of the combobox. So I would
think all you need to do is disable the filter if the combo does not
contain a value and set...

Me.Filter = "VehicleNo='" & Me!VehicleSearch & "'"
Me.FilterOn = True

if it does. Unless I'm missing something, your remaining code is
unnecessary.