Access and VB

[Douglas J. Steele]

I wouldn't think so.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thank you,

Just one question do I need to add the code in under an event when the
page loads???

Nothing comes to mind immediately. I'll ping Allen, who suggested that,
for you and see whether he wants to jump back into this discussion!

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Ok that now goes through without any errors I ahve also done the
following

Once you have it working, if you have a form with a field
named ProductID, you can add a text box to your from, and set
its Control Source property to:
=OnHand([ProductID])

 

[Simon Glencross]

OK, well I shall wait and hope Alan responds......

Thank you

I wouldn't think so.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thank you,

Just one question do I need to add the code in under an event when the
page loads???

Nothing comes to mind immediately. I'll ping Allen, who suggested that,
for you and see whether he wants to jump back into this discussion!

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message Ok that now goes through without any errors I ahve also done the
following

Once you have it working, if you have a form with a field
named ProductID, you can add a text box to your from, and
set its Control Source property to:
=OnHand([ProductID])

 

[Allen Browne]

Hi Simon

I take it that:
a) You have this code in a standard module (one that can be reached through
the Modules tab of the Database window.)

b) It compiles okay.

c) You get the correct quantity returned for product 99 when you open the
Immediate Window (Ctrl+G) and enter something like this:
? OnHand(99)

d) You have a form bound to a table/query that has a ProductID field, and on
that form you have a text box with Control Source:
=OnHand([ProductID])

Can you verify that these assumptions are correct, and indicate what result
you get in the text box: e.g. blank, #Error, other?

 

[Simon Glencross]

Allen,

Thank you for you help I have managed to get it up and working!!

Can I ask you another questiong re your AppInventory?

I was to create a report based on the stock tootals created what is the best
way of doing this as the code which you provide requires for you to enter
the prouct code in order to get a total.

Any advice is much appreciated!

Thanks again

Hi Simon

I take it that:
a) You have this code in a standard module (one that can be reached
through the Modules tab of the Database window.)

b) It compiles okay.

c) You get the correct quantity returned for product 99 when you open the
Immediate Window (Ctrl+G) and enter something like this:
? OnHand(99)

d) You have a form bound to a table/query that has a ProductID field, and
on that form you have a text box with Control Source:
=OnHand([ProductID])

Can you verify that these assumptions are correct, and indicate what
result you get in the text box: e.g. blank, #Error, other?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

OK, well I shall wait and hope Alan responds......

Thank you

 

[Allen Browne]

Create a query based on your table of products.

In the query, type something like this into a fresh column in the Field row:
InStock: OnHand([ProductID])

Create a report from the query.

 

[Simon Glencross]

Thank you, I ahve managed to create the report but I have a problem. When I
debug it errors with a data type mismatch on the following lngProduct =
itemCode my itemcode is a text field so I know I need to incorporate ' '
in somewhere but I cant work out where??

Thanks in advance

Create a query based on your table of products.

In the query, type something like this into a fresh column in the Field
row:
InStock: OnHand([ProductID])

Create a report from the query.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

Thank you for you help I have managed to get it up and working!!

Can I ask you another questiong re your AppInventory?

I was to create a report based on the stock tootals created what is the
best way of doing this as the code which you provide requires for you to
enter the prouct code in order to get a total.

Any advice is much appreciated!

Thanks again

 

[Allen Browne]

In the code, replace:
Dim lngProduct As Long
to:
Dim strProduct As String

Replace every occurrance of:
lngProduct
with:
strProduct

Everywhere you concatenate the ProductID into a string, add the quotes, e.g.
change:
"WHERE ((tblAcqDetail.ProductID = " & lngProduct & ")"
to:
"WHERE ((tblAcqDetail.ProductID = """ & strProduct & """)"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thank you, I ahve managed to create the report but I have a problem. When
I debug it errors with a data type mismatch on the following
lngProduct = itemCode my itemcode is a text field so I know I need to
incorporate ' ' in somewhere but I cant work out where??

Thanks in advance

Create a query based on your table of products.

In the query, type something like this into a fresh column in the Field
row:
InStock: OnHand([ProductID])

Create a report from the query.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

Thank you for you help I have managed to get it up and working!!

Can I ask you another questiong re your AppInventory?

I was to create a report based on the stock tootals created what is the
best way of doing this as the code which you provide requires for you to
enter the prouct code in order to get a total.

Any advice is much appreciated!

Thanks again