Problem with form and coding

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Kelvin Leong - 20 Mar 2007 07:50 GMT

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

Reply to this Message

storrboy - 20 Mar 2007 12:52 GMT

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.

Reply to this Message

Kelvin Leong - 20 Mar 2007 13:50 GMT

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,
>
[quoted text clipped - 8 lines]
> if it does. Unless I'm missing something, your remaining code is
> unnecessary.

Reply to this Message

Kelvin Leong - 20 Mar 2007 13:52 GMT

> Hey Kevin,
>
[quoted text clipped - 8 lines]
> if it does. Unless I'm missing something, your remaining code is
> unnecessary.

Reply to this Message

storrboy - 22 Mar 2007 03:59 GMT

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

Reply to this Message

Peter Hibbs - 22 Mar 2007 15:24 GMT

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%20Grid%20D
emo.mdb'

HTH

Peter Hibbs.

>Hi Storrboy,
>
[quoted text clipped - 38 lines]
>> if it does. Unless I'm missing something, your remaining code is
>> unnecessary.

Reply to this Message

Kelvin Leong - 23 Mar 2007 09:54 GMT

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
>
[quoted text clipped - 50 lines]
> >> if it does. Unless I'm missing something, your remaining code is
> >> unnecessary.

Reply to this Message

Peter Hibbs - 23 Mar 2007 10:40 GMT

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,
>
[quoted text clipped - 78 lines]
>> >> if it does. Unless I'm missing something, your remaining code is
>> >> unnecessary.

Reply to this Message

AccessVandal - 23 Mar 2007 10:52 GMT

Hi,

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

>Kelvin Leong wrote:

>CaseID | VehicleNo | Col1 | Col2 | Subtotal
>-------------------------------------------------------------
[quoted text clipped - 10 lines]
>do I prevent the form from popping-up the "Enter Parameter Value" for Col1
>and Col2?

Reply to this Message

Douglas J. Steele - 23 Mar 2007 11:59 GMT

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

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Dear Peter,
>
[quoted text clipped - 84 lines]
>> >> if it does. Unless I'm missing something, your remaining code is
>> >> unnecessary.

Reply to this Message

Kelvin Leong - 23 Mar 2007 13:06 GMT

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.
[quoted text clipped - 92 lines]
> >> >> if it does. Unless I'm missing something, your remaining code is
> >> >> unnecessary.

Reply to this Message