Pick/Basic application integration with access.(not waving, drowning) 1

[petrosky]

Hi there,

I am an Access Novice.

We have an Accuterm Pick/Basic Point of Sale System.

I have just worked out how to connect to it via Access (it came bundled with a Macro/Module which I simply copied)

I have a table with productcode and using these prod numbers I can "PICKREAD" other attributes from the POS system such as Prod. description, Cost price, stock on hand etc. which I also have as columns of my table.

I have a form where I have text boxes which have as their Control Source

= Pickread("ivmst",[productcode],3)

where "ivmst" is a library? productcode is a row in my table and 3 is an attribute of that library (in this case the prod. description.)

This works a treat and updates the relevant text boxes with the correct data although the results don't store in my table.

My question is this...Can I call this function from a Query or use it as criteria for my query...I have tried adding this into the criteria box of my query

pickread("ivmst",[productcode],3)
or
=pickread("ivmst",[productcode],3)

Is there something I am doing wrong when refering to this expression?


Any help or ideas will be appreciated.

Peter.

 

[Maquis]

If PickRead is a function in your database you should be able to just enter it in the criteria section of the query. (No equal sign). Since it works in your form, the query should also recognize it.

However, we also have a PICK system here and I would love to be able to it's database from Access. If possible I would love to get a copy of that macro/module that you have. Maq B-)
<insert witty signature here>

 

[petrosky]

Hi Maquis,

Thanks for the reply.

As to you being able to access your PICK system...it would depend AFAIK on the &quot;server&quot; programs bundled with it in the backend. Our system shipped with FTServer which is what I am using.

Essentially this puts the client computer into a server mode and allows you to &quot;call&quot; functions etc (if you know what you are doing of course) I had been taking TCL reports and making text files from them, then importing them into excel/access which is quite timeconsuming.

As someone who until quite recently thought VB was a beer (Victoria Bitter) I am struggling to get a grip on VBA.

I can drop the module to you via email if you like.

prissco@bigpond.com is my addy.


Regards,

Peter.

 

[petrosky]

Maquis,

Almost forgot...in Access VBA window I also added a reference to Accuterm Pickserver...don't know if this is important or not.

Peter

 

[Maquis]

Petrosky, thanks for the reply.

I've been checking around here some and it seems we do have 1 web application which is already using routines to access our Pick system. I believe it can be adapted for Access, however our Web app is still in the testing phase and our boss would rather not have more apps hitting against the Pick computer until the web app comes up and is stable. However I've gotten a copy of their code and am currently perusing it hoping to learn something.

I hope you get your query to work. One other thing that I thought of. Since the function works in your form, but not in the query make sure it's not a private function that is visible only to the form. It should be in a module. Maq B-)
<insert witty signature here>

 

[petrosky]

Hi agian Maq,

My company also sees this as a goal we are working towards...IE. Out website linking/updating from our PICK system as well as pricelist automation.

As to my Query...I will try to explain the situation a little better

My Table has these fields.

ID(P/Key) ProdCode ProdDesc ListCost AVGCost STockHolding
1 56254 Canon xxx $10 $9 14

The ProdCode field as it stands now is imported from EXCEL and the other values should be able to be &quot;PickRead&quot; from the FTServer.

When the query is run it asks for the server name as it should then returns nothing...could this be because of null values?
My whole table structure is sad as the only field propagated is the Prodcode (from excel) doh!.

I would be interested to hear how you get on with your PICK integration w/ access.

Peter.

 

[Maquis]

Well, my PICK integration seems to be down the road a ways. I'll post here though, when my boss gives the go ahead for my project. (In the meantime I have more than enough work to keep me busy.)

Without understanding the specifics of how your PICK routine works it's hard to advise you on how to fix your query. Have you tried stepping through it in the debugger. See if the query is even getting into the function. If the problem is that the function is returning nulls then check out some things in PICK. Over here I know that we had some problems with the DICTs in PICK. They had to be worded just right before VB would pick up data from them. Maq B-)
<insert witty signature here>

 

[petrosky]

Maq,

here is the SQL of my query...

SELECT Peteres.ID, Peteres.ProdCode, Peteres.ProdDesc, Peteres.StockonHand, Peteres.AvgCost, Peteres.ListCost, Peteres.Group
FROM Peteres
WHERE (((Peteres.StockonHand)=pickread(&quot;ivmst&quot;,[ProdCode],17)) AND ((Peteres.AvgCost)=pickread(&quot;ivmst&quot;,[ProdCode],7)) AND ((Peteres.ListCost)=pickread(&quot;ivmst&quot;,[ProdCode],8)));

here is my module

ps it takes 20 minutes to run (only to return no products)


Regards

Peter

Option Compare Database
Option Explicit

Private PickServerObject As atPickServer.Server
Private PickServerAbort As Boolean

Public Function PickRead(FileName As String, ID As String, Optional ByVal Attr As Integer = 0, Optional ByVal Val As Integer = 0, Optional ByVal SubVal As Integer = 0, Optional ByVal Conv As String = &quot;&quot As String
Dim svr As String
Dim errnum As Long
On Error Resume Next
If PickServerObject Is Nothing Then
svr = InputBox(&quot;Please enter the server name if you want to connect to a specific server.&quot;, _
&quot;Connect to Pick Server&quot;, &quot;&quot
Set PickServerObject = CreateObject(&quot;atPickServer.Server&quot
If Not PickServerObject Is Nothing Then
Do
If PickServerObject.Connect(svr) Then
Exit Do
Else
Select Case MsgBox(&quot;Unable to connect to server. Do you want to try connecting again?&quot;, vbAbortRetryIgnore, &quot;Connect to Pick Server&quot
Case vbAbort
Set PickServerObject = Nothing
PickServerAbort = True
Exit Function
Case vbCancel
Set PickServerObject = Nothing
Exit Function
End Select
End If
Loop
End If
End If
If PickServerObject Is Nothing Then Exit Function
PickRead = PickServerObject.ReadItem(FileName, ID, Attr, Val, SubVal)
errnum = PickServerObject.LastError
If errnum <> 0 And errnum <> 202 Then 'Ignore Not-on-File messages
PickRead = PickServerObject.LastErrorMessage
End If
If Len(Conv) Then PickRead = PickServerObject.OConv(PickRead, Conv)
End Function

Public Function Val(x As Variant) As Currency
On Error Resume Next
Val = VBA.Val(x)
End Function

 

[Maquis]

Petrosky, does the Pickread function work when you reference it in a form? I can see nothing wrong with the SQL statement you posted, so that implies something is wrong with the Pickread function. Unfortunately, I'm not qualified to tell you what that something is.

The only useful suggestion I can make is to put lots of msgbox's in the Pickread function to track it's progress and make sure it's doing what you expect when you expect it.

I hope you get it solved! Maq B-)
<insert witty signature here>