WHATS WRONG WITH THIS CODE??

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi Al

Wondered if you could help. I've got two combo boxes on a form. Once you've selected a value from first, second one is requeried and a value is displayed in the field below. However, whilst the requery on the combo is working the field is displaying the wrong result. I have written the following code to try and link the field to return a value depending on what is selected in both combo boxes but I am stumped as to where I'm going wrong. The fact that I'm a total novice to VB and SQR doesn't help

The code is as follows

Private Sub Combo9_AfterUpdate(
[matQRY SubForm].Requer

' Find the record that matches the control
Me.Price.RowSource = "SELECT Price FROM" &
" MatQry WHERE Material = " & Me.Combo7 &
" MatQry WHERE Colour = " & Me.Combo9 &
Me.Price = Me.Price.ItemData(0

When I choose valued from the two combo boxes and error is displayed saying

Compile error: Method or data member not found. And the second off last Me.Price is highlighted in the debugger screen

Can anyone explain to me what I'm doing wrong and if indeed, this is the correct way of doing this. HELP!

Many thanks to anyone who responds

Donna
 
Hi,

It is a typo ? The last line should be like


"Me.Price = " & Me.Price.ItemData(0)


while we read here


Me.Price = Me.Price.ItemData(0)


I also assume each of the value is not NULL:



"Me.Price = " & Nz( Me.Price.ItemData(0), "NULL" )


Otherwise, if Me.Price.ItemData(0) is Null, then


"Me.Price = " & Me.Price.ItemData(0)

evaluates to

"Me.Price = "


and that is it, a not very nice "criteria"... Note that adding the Nz would
evaluates to

"Me.Price=Null"

which ALWAYS returns NULL ( which is NEITHER TRUE, neither false).




Hoping it may help,
Vanderghast, Access MVP




Donna W said:
Hi All

Wondered if you could help. I've got two combo boxes on a form. Once
Click to expand...
you've selected a value from first, second one is requeried and a value is
displayed in the field below. However, whilst the requery on the combo is
working the field is displaying the wrong result. I have written the
following code to try and link the field to return a value depending on what
is selected in both combo boxes but I am stumped as to where I'm going
wrong. The fact that I'm a total novice to VB and SQR doesn't help.
The code is as follows:

Private Sub Combo9_AfterUpdate()
[matQRY SubForm].Requery

' Find the record that matches the control.
Me.Price.RowSource = "SELECT Price FROM" & _
" MatQry WHERE Material = " & Me.Combo7 & _
" MatQry WHERE Colour = " & Me.Combo9 & _
Me.Price = Me.Price.ItemData(0)

When I choose valued from the two combo boxes and error is displayed saying:

Compile error: Method or data member not found. And the second off last
Click to expand...
Me.Price is highlighted in the debugger screen.
Can anyone explain to me what I'm doing wrong and if indeed, this is the
Click to expand...
correct way of doing this. HELP!!
 
Thanks for your help. I've changed added the quotes around me.price but now I get an error with regards to Me.Combo9 which is highlighted in debugger, with the message

Compile error: Method or data member not found

To be quite honest, when I said I was a complete novice I meant it and in actual fact, I'm not quite sure what I'm saying in the whole of the statement. Don't suppose you could tell me what each line is basically telling the system. Sorry to be a pain. My knowledge of coding is very poor but I've been struggling with this for 3 hours per day x 4 and am still at the same place where I started - it doesn't work!

I do appreciate your help - thank you

Donna
 
Hi,


The whole line should be

Me.Price.RowSource = "SELECT Price FROM" & _
" MatQry WHERE Material = " & Me.Combo7 & _
" AND Colour = " & Me.Combo9 & _
" AND Price = " & Me.Price.ItemData(0)


where I assume you have a field Material, Colour and Price in your table (or
query) MatQry, and that those three fields are numerical (not string). If
Colour is a string, try


Me.Price.RowSource = "SELECT Price FROM" & _
" MatQry WHERE Material = " & Me.Combo7 & _
" AND Colour = '" & Me.Combo9 & "'" _
" AND Price = " & Me.Price.ItemData(0)


Also, I find unusual to change the row source of control Price, while the
data making that row source is itself dependant of itself.



Don't hesitate to reply here if there is any question...

Vanderghast, Access MVP


Donna W said:
Thanks for your help. I've changed added the quotes around me.price but
Click to expand...
now I get an error with regards to Me.Combo9 which is highlighted in
debugger, with the message:
Compile error: Method or data member not found.

To be quite honest, when I said I was a complete novice I meant it and in
Click to expand...
actual fact, I'm not quite sure what I'm saying in the whole of the
statement. Don't suppose you could tell me what each line is basically
telling the system. Sorry to be a pain. My knowledge of coding is very
poor but I've been struggling with this for 3 hours per day x 4 and am still
at the same place where I started - it doesn't work!!
 
Hi,


Sorry, if colour is a string:


Me.Price.RowSource = "SELECT Price FROM" & _
" MatQry WHERE Material = " & Me.Combo7 & _
" AND Colour = '" & Me.Combo9 & _
"' AND Price = " & Me.Price.ItemData(0)



there is a single quote after the starting double quote in the last line.


Vanderghast, Access MVP
 
Back
Top