What is wrong with this code -

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

Hi,

I got the code needed to do this from the Access website
and modified it to fit what I nneded it to do for me. IT
worked for the firt two statements, but when I added
more, I get runtime error, that I have canceled pervoius
operation.

Could you please see if there is any wrong doing here in
the code.

Thanks,

Mike
 
Mike said:
Then, Why is the error message I'm getting?

Thx,

Mike
Click to expand...

Mike, I'm not sure you understand what Mark Phillipson was telling you.
You didn't post the code in your message, so there's no way anyone can
tell you whatr's wrong with it. It may be that you posted the code in
question in a separate message, but it's too much to expect readers to
hunt through all the messages in the newsgroup to find the one where
(maybe) you posted the code you're talking about in this message.
 
i'm very very sorry guys. Here it is. I have put it in
the on exit propertry of the ProductID on my Inventory
form bounded to a table "tblProduct"

I want the information in "tblProductsIn" to be filledin
when the productID is selected. Basically, same
information needs to be entered into table product plus
some extra fields.


Private Sub ProductID_Exit(Cancel As Integer)
' Dim varProductName, varProductDescription As Variant
Dim varProductName, varProductDescription, Supplier,
UnitsIn, DateReceived, UnitPrice As Variant
varProductName = DLookup
("ProductName", "tblProductsIn", "ProductID =
[ProductID] ")
varProductDescription = DLookup
("ProducDescription", "tblProductsIn", "ProductID =
[ProductID] ")
varSupplier = DLookup
("Supplier", "tblProductsIn", "ProductID = [ProductID] ")
varUnitsIn = DLookup
("UnitsIn", "tblProductsIn", "ProductID = [ProductID] ")
varDateReceived = DLookup
("DateReceived", "tblProductsIn", "ProductID =
[ProductID] ")
varUnitPrice = DLookup
("UnitPrice", "tblProductsIn", "ProductID = [ProductID] ")

If (Not IsNull(varProductName)) Then Me![ProductName]
= varProductName
If (Not IsNull(varProductDescription)) Then Me!
[ProductDescription] = varProductDescription
If (Not IsNull(varSupplier)) Then Me![Supplier] =
varSupplier
If (Not IsNull(varUnitsIn)) Then Me![UnitsIn] =
varUnitsIn
If (Not IsNull(varDateReceived)) Then Me!
[DateReceived] = varDateReceived
If (Not IsNull(varUnitPrice)) Then Me![UnitPrice] =
varUnitPrice

End Sub

Again, sorry that I goofed.

Mike
 
Mike said:
i'm very very sorry guys. Here it is. I have put it in
the on exit propertry of the ProductID on my Inventory
form bounded to a table "tblProduct"

I want the information in "tblProductsIn" to be filledin
when the productID is selected. Basically, same
information needs to be entered into table product plus
some extra fields.


Private Sub ProductID_Exit(Cancel As Integer)
' Dim varProductName, varProductDescription As Variant
Dim varProductName, varProductDescription, Supplier,
UnitsIn, DateReceived, UnitPrice As Variant
varProductName = DLookup
("ProductName", "tblProductsIn", "ProductID =
[ProductID] ")
varProductDescription = DLookup
("ProducDescription", "tblProductsIn", "ProductID =
[ProductID] ")
varSupplier = DLookup
("Supplier", "tblProductsIn", "ProductID = [ProductID] ")
varUnitsIn = DLookup
("UnitsIn", "tblProductsIn", "ProductID = [ProductID] ")
varDateReceived = DLookup
("DateReceived", "tblProductsIn", "ProductID =
[ProductID] ")
varUnitPrice = DLookup
("UnitPrice", "tblProductsIn", "ProductID = [ProductID] ")

If (Not IsNull(varProductName)) Then Me![ProductName]
= varProductName
If (Not IsNull(varProductDescription)) Then Me!
[ProductDescription] = varProductDescription
If (Not IsNull(varSupplier)) Then Me![Supplier] =
varSupplier
If (Not IsNull(varUnitsIn)) Then Me![UnitsIn] =
varUnitsIn
If (Not IsNull(varDateReceived)) Then Me!
[DateReceived] = varDateReceived
If (Not IsNull(varUnitPrice)) Then Me![UnitPrice] =
varUnitPrice

End Sub

Again, sorry that I goofed.
Click to expand...

No problem.

This is very inefficient code, Mike. You'd do much better to use a
recordset to look up all the fields at once, if in fact you have to look
them up at all (why not just bind the form to a query that joins to
tblProductsIn on the ProductID field?). However, I suspect that the
error message you're getting is from having misspelled one of the field
names that you're trying to look up. Is the name of the product
description field really "ProducDescription", and not
"ProductDescription"? Even if it is, check all the other field names
for a misspelling.
 
Dirk,

Thanks for pinpointing the typo, that was the problem. If
I wanted to proceede with your suggestion on using
recordset, how would I need to go about and chang my
codes?

Regards,

Mike
-----Original Message-----
Mike said:
i'm very very sorry guys. Here it is. I have put it in
the on exit propertry of the ProductID on my Inventory
form bounded to a table "tblProduct"

I want the information in "tblProductsIn" to be filledin
when the productID is selected. Basically, same
information needs to be entered into table product plus
some extra fields.


Private Sub ProductID_Exit(Cancel As Integer)
' Dim varProductName, varProductDescription As Variant
Dim varProductName, varProductDescription, Supplier,
UnitsIn, DateReceived, UnitPrice As Variant
varProductName = DLookup
("ProductName", "tblProductsIn", "ProductID =
[ProductID] ")
varProductDescription = DLookup
("ProducDescription", "tblProductsIn", "ProductID =
[ProductID] ")
varSupplier = DLookup
("Supplier", "tblProductsIn", "ProductID = [ProductID] ")
varUnitsIn = DLookup
("UnitsIn", "tblProductsIn", "ProductID = [ProductID] ")
varDateReceived = DLookup
("DateReceived", "tblProductsIn", "ProductID =
[ProductID] ")
varUnitPrice = DLookup
("UnitPrice", "tblProductsIn", "ProductID = [ProductID] ")

If (Not IsNull(varProductName)) Then Me! [ProductName]
= varProductName
If (Not IsNull(varProductDescription)) Then Me!
[ProductDescription] = varProductDescription
If (Not IsNull(varSupplier)) Then Me![Supplier] =
varSupplier
If (Not IsNull(varUnitsIn)) Then Me![UnitsIn] =
varUnitsIn
If (Not IsNull(varDateReceived)) Then Me!
[DateReceived] = varDateReceived
If (Not IsNull(varUnitPrice)) Then Me![UnitPrice] =
varUnitPrice

End Sub

Again, sorry that I goofed.
Click to expand...

No problem.

This is very inefficient code, Mike. You'd do much better to use a
recordset to look up all the fields at once, if in fact you have to look
them up at all (why not just bind the form to a query that joins to
tblProductsIn on the ProductID field?). However, I suspect that the
error message you're getting is from having misspelled one of the field
names that you're trying to look up. Is the name of the product
description field really "ProducDescription", and not
"ProductDescription"? Even if it is, check all the other field names
for a misspelling.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.
Click to expand...
 
Mike said:
Dirk,

Thanks for pinpointing the typo, that was the problem. If
I wanted to proceede with your suggestion on using
recordset, how would I need to go about and chang my
codes?
Click to expand...

I note that John Viescas has also addressed this issue in a separate
thread, and made the very good suggestion of picking up extra columns
from a combo box. That's a better solution than doing a lookup, but for
completeness I'll give some example code to do what I was talking about:

'----- start of sample code -----
Private Sub ProductID_AfterUpdate()

Dim rs As DAO.Recordset
Dim strSQL As String

strSQL =
"SELECT * FROM tblProductsIn " & _
"WHERE ProductID=" & Me.ProductID

Set rs = CurrentDb.OpenRecordset(strSQL)

With rs
If .EOF Then
Msgbox "Product ID not on file!"
Else
Me.ProductName = !ProductName
Me.ProductDescription = !ProductDescription
Me.Supplier= !Supplier
Me.UnitsIn = !UnitsIn
Me.DateReceived = !DateReceived
Me.UnitPrice = !UnitPrice
End If
.Close
End With

Set rs = Nothing

End Sub
'----- end of sample code -----

Note: the above code requires you to have a reference set to the DAO
object library. If you're using Access 97, you already have the
reference set by default. If you're using A2K or later, you may need to
click Tools -> References... in the VB Editor, locate Microsoft DAO 3.6
Object Library in the list, and put a check mark in the box next to it.

Note also that I moved the code from the Exit event to the AfterUpdate
event, which is a better choice for this sort of processing.
 
Dirk,

Thanks very much for the code. I have put it in the
AfterUpdate property of the ProductID, but the following
statement is "red" in the VB editor. I have also check
the Microsoft DAO 3.6 Object Library. I had to uncheck
the 3.5 library to add this one. what confuses me, I have
this 3.6 checked eversince I started this application.

strSQL =
"SELECT * FROM tblProductsIn " & _
"WHERE ProductID=" & Me.ProductID

I thank you for your help.

Mike
 
Dirk,

Well, I fixed that issue by back spacing the line but I'm
facing a new Run-Time error. first, sOme other codes
(code for my switchboard didn't work and I changed rst to
rs and took care of that. Now when I, update the
ProductID feild on my form, I get:

Error code 3061 and Too few parameters.expected1

Any thoughts on this?

Thanks,

Mike
 
Mike said:
Dirk,

Thanks very much for the code. I have put it in the
AfterUpdate property of the ProductID, but the following
statement is "red" in the VB editor. I have also check
the Microsoft DAO 3.6 Object Library. I had to uncheck
the 3.5 library to add this one. what confuses me, I have
this 3.6 checked eversince I started this application.

strSQL =
"SELECT * FROM tblProductsIn " & _
"WHERE ProductID=" & Me.ProductID
Click to expand...

I'm puzzled as to why the line would be flagged by the VB Editor as in
error; it looks fine to me, and I know you must have a control named
ProductID because that name was embedded in the event procedure name you
first posted. Try typing in the line from scratch (watching out for
typos, of course).

You say you had to uncheck a reference to DAO 3.5? What version of
Access are you working in? If you're working in Access 2000 or later,
you should be using DAO 3.6, and I don't know how the reference to DAO
3.5 got in there. If you're working in Access 97, then you should have
a reference to DAO 3.51, and *not* to DAO 3.6.
 
Mike said:
Dirk,

Well, I fixed that issue by back spacing the line but I'm
facing a new Run-Time error. first, sOme other codes
(code for my switchboard didn't work and I changed rst to
rs and took care of that. Now when I, update the
ProductID feild on my form, I get:

Error code 3061 and Too few parameters.expected1

Any thoughts on this?
Click to expand...

I don't understand what you're saying about other code errors, but I
don't see how they could be related to what we've been doing, except
maybe if there's a broken reference. As for the error message, what
type of field (in the table) is ProductID? I was assuming it was a
number or autonumber field. If it's a text field, then the SQL
statement must be modified to put the ProductID in quotes:

strSQL =
"SELECT * FROM tblProductsIn " & _
"WHERE ProductID='" & Me.ProductID & "'"
 
Thanks Dirk,

The ProductID feild in both of my tables are of type
text. I modified the code based on you correction but
still I get this error, Method or data member not found
and the first statement gets hightlighted as below:
!Me.ProductName = !ProductName (this gets highlighted)
!Me.ProductDescription = !ProductDescription
!Me.Supplier = !Supplier
!Me.UnitsIn = !UnitsIn
!Me.DateReceived = !DateReceived
!Me.UnitPrice = !UnitPrice
I would appreciate your help.

Regards,

Mike
 
Dirk,

I'm working in Access for Office XP (2002) and the 3.5
library was there and checked. Of course its now
unchecked, but I can say for sure that I have 3.6 library
checked when I first started this application and I have
codes in there for times, switchboard, etc.

Thanks for your help.

Regards,

Mike
 
Mike said:
Thanks Dirk,

The ProductID feild in both of my tables are of type
text. I modified the code based on you correction but
still I get this error, Method or data member not found
and the first statement gets hightlighted as below:
!Me.ProductName = !ProductName (this gets highlighted)
!Me.ProductDescription = !ProductDescription
!Me.Supplier = !Supplier
!Me.UnitsIn = !UnitsIn
!Me.DateReceived = !DateReceived
!Me.UnitPrice = !UnitPrice
I would appreciate your help.
Click to expand...

That's not the code I posted. Take away the first exclamation point on
each line quoted above -- the keyword Me should not be prefixed with an
exclamation point. Only the field names after the equals sign should
have the "!" prefix.
 
Dirk,

You are right, I corrected them, but I still have this
problem in term of the the object:

Object doesn't support the data or method where in the
debug mide I can see the correct data at the other side
of the equal sign when I point to them by mouse.

Private Sub ProductID_AfterUpdate()

Dim rs As DAO.Recordset
Dim strSQL As String
'Since ProductID is a textfeild and not a number it must
be in quotes

strSQL = "SELECT * FROM tblProductsIn " & _
"WHERE ProductID ='" & Me.ProductID & "'"


Set rs = CurrentDb.OpenRecordset(strSQL)

With rs
If .EOF Then
MsgBox "Product ID not on file!"
Else
Me.ProductName = !ProductName
Me.ProductDescription = !ProductDescription
Me.Supplier = !Supplier
Me.UnitsIn = !UnitsIn
Me.DateReceived = !DateReceived
Me.UnitPrice = !UnitPrice
End If
.Close
End With

Set rs = Nothing

End Sub
'----- end of sample code -----


here I cannot see of why all the registeries have correct
values in them but I still get the error message.

You help and attension is greatly appreciated. This is a
learinig process for me and is a good one.

Regards,

Mike
 
Mike said:
Dirk,

You are right, I corrected them, but I still have this
problem in term of the the object:

Object doesn't support the data or method where in the
debug mide I can see the correct data at the other side
of the equal sign when I point to them by mouse.

Private Sub ProductID_AfterUpdate()

Dim rs As DAO.Recordset
Dim strSQL As String
'Since ProductID is a textfeild and not a number it must
be in quotes

strSQL = "SELECT * FROM tblProductsIn " & _
"WHERE ProductID ='" & Me.ProductID & "'"


Set rs = CurrentDb.OpenRecordset(strSQL)

With rs
If .EOF Then
MsgBox "Product ID not on file!"
Else
Me.ProductName = !ProductName
Me.ProductDescription = !ProductDescription
Me.Supplier = !Supplier
Me.UnitsIn = !UnitsIn
Me.DateReceived = !DateReceived
Me.UnitPrice = !UnitPrice
End If
.Close
End With

Set rs = Nothing

End Sub
'----- end of sample code -----


here I cannot see of why all the registeries have correct
values in them but I still get the error message.
Click to expand...

It would help to know which line is highlighted as causing the error,
when you go into debug mode. The only thing I can think of is that you
don't have a control on your form, or a field in your form's
recordsource, with the same name as one the fields in the recordset you
opened. In writing that code, I assumed that you had controls with the
same names as the fields in tblProductsIn, but it may be that your
controls have other names. Check the names of all the controls you're
assigning to.
 
Dirk,

It works now. I have to delete the form and re-make it.
something on the form wasn't right and I couldn't see
what, my guess is some value in the memory that was not
clearing but it work fine and thank you.

I have one more question if no trouble. How would you go
about in setting timers, based on date and time for
printing Inventory to avoid overlapping of items
resulting in wrong report on the Inventory?

I don't know if I could present my question in a right
manner or not. your thoughs and help is greatly
appreciated.

Mike
 
Mike said:
Dirk,

It works now. I have to delete the form and re-make it.
something on the form wasn't right and I couldn't see
what, my guess is some value in the memory that was not
clearing but it work fine and thank you.
Click to expand...

You're welcome. I'm glad to hear you got it working.
I have one more question if no trouble. How would you go
about in setting timers, based on date and time for
printing Inventory to avoid overlapping of items
resulting in wrong report on the Inventory?

I don't know if I could present my question in a right
manner or not. your thoughs and help is greatly
appreciated.
Click to expand...

I'm afraid I can't understand your question, but since it is clearly
completely unrelated to the original question we've been discussing,
please post it as new question with a meaningful subject line. I think
you're going to have to go into more detail in describing the problem,
though, as I doubt many people will understand the question as you wrote
it above.
 
Back
Top