Code doesn't work, why? 
Author Message
 Code doesn't work, why?

I'm new to VB,  so I apologize if this is silly
I set up a form to enter vehicle usage reports in a table.
The form has a combo box that receives a list of vehicles from the vehicle
table.
When this vehicle is selected, I would like to enter some values in other
textboxes automatically,
I am using an SQL statement that works in a query, but I can't get it to
enter what I want in the form "after update"
this is the code:

Private Sub VehicleID_AfterUpdate()
   Dim strSQL As String
   strSQL = "SELECT Max(Mileage.LOF) AS Expr1 FROM Mileage WHERE
(((Mileage.[Vehicle ID])=[Forms]![Mileage]![VehicleID]));"
LOF.Value = strSQL
End Sub

I also tried "LOF.defaultValue = strSQL"
Does it matter that LOF is a number?



Mon, 23 Aug 2004 23:58:01 GMT  
 Code doesn't work, why?

Quote:

>I'm new to VB,  so I apologize if this is silly
>I set up a form to enter vehicle usage reports in a table.
>The form has a combo box that receives a list of vehicles from the vehicle
>table.
>When this vehicle is selected, I would like to enter some values in other
>textboxes automatically,
>I am using an SQL statement that works in a query, but I can't get it to
>enter what I want in the form "after update"
>this is the code:

>Private Sub VehicleID_AfterUpdate()
>   Dim strSQL As String
>   strSQL = "SELECT Max(Mileage.LOF) AS Expr1 FROM Mileage WHERE
>(((Mileage.[Vehicle ID])=[Forms]![Mileage]![VehicleID]));"
>LOF.Value = strSQL
>End Sub

>I also tried "LOF.defaultValue = strSQL"
>Does it matter that LOF is a number?

the problem is that this code looks for a value called
"[Forms]![Mileage]![VehicleID]"  and you want it to look for the value
contained in  [Forms]![Mileage]![VehicleID]

try this

Dim rst as recordset
set rst=currentdb.openrecordset("SELECT Max(Mileage.LOF) AS Expr1 FROM
Mileage WHERE (((Mileage.[Vehicle ID]) = " &
[Forms]![Mileage]![VehicleID]  & "));"
LOF = rst!Expr1

this assumes that VehicleID is numeric only
if string you need extra single quotes (or apostrophe ' )

Mileage WHERE (((Mileage.[Vehicle ID]) = '" &
 [Forms]![Mileage]![VehicleID]  & "'));"

also,  you could just as easily (actually, more easily) use the DMax
function.   Look it up in the Help.

(the above is not tested, coming straight from my memory cells, which
are not perfect)

----------------
Richard "ManxMan" Killey

www.comeandread.com/access

visit my site for tips



Tue, 24 Aug 2004 00:23:38 GMT  
 Code doesn't work, why?


Quote:
> I'm new to VB,  so I apologize if this is silly
> I set up a form to enter vehicle usage reports in a table.
> The form has a combo box that receives a list of vehicles from the vehicle
> table.
> When this vehicle is selected, I would like to enter some values in other
> textboxes automatically,
> I am using an SQL statement that works in a query, but I can't get it to
> enter what I want in the form "after update"
> this is the code:

> Private Sub VehicleID_AfterUpdate()
>    Dim strSQL As String
>    strSQL = "SELECT Max(Mileage.LOF) AS Expr1 FROM Mileage WHERE
> (((Mileage.[Vehicle ID])=[Forms]![Mileage]![VehicleID]));"
> LOF.Value = strSQL
> End Sub

> I also tried "LOF.defaultValue = strSQL"
> Does it matter that LOF is a number?

Have you tried making the query for the combo box control contain all of the
fields that you want to update on your form (you don't have to see them when
the combo box opens set the column lengths to 0") then defualt value of the
controls to the particular combo box column.

for example say query contains;

vehicle id; operator; beginning miles (dmax on vehicle table); modle; etc

in the after update code:
if  vehicleId.column(2) <>"" then
me!operator = cboVehicleId.column(2)
else
me!operator = ""
end if

if VehicleId.column(3) <> "" then
me!milage = vehicleId.column(3)
else
me!milage = ""
end if

and so on.

obviously the if then parts would not be necessary if the particular field
is never empty
the combo box field count property must be set to the number of fields in
the query or else
the controls will appear blank.

this is for controls that can be changed by the user on the form if you
simply want to insert default values, if the values are just for info but
are not to be changed you don't need to write any code
just create an unbound text box on the form and set its control source to =
me!VehicleId.column(x)

is this of any help?



Tue, 24 Aug 2004 11:27:26 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. why this code doesn't work?

2. Why doesn't these codes work?

3. Validate Code - Why doesn't this work!?

4. why doesn't this code work?

5. Why doesn't this code work on win 2000

6. why this code doesn't work??

7. Why doesn't this simple code work!!!!

8. Why doesn't this code work?

9. Why this code doesn't work?

10. Why doesn't this code work???

11. Why doesn't this code work?

12. Why this code doesn't work??

 

 
Powered by phpBB® Forum Software