We help IT Professionals succeed at work.

How do I insert data into multiple fields for one record in Access form from Winwedge?

InlinePlastics asked
I currently have an Access form that gets weight data from a scale when the user presses the scale's Print button.  I am using Winwedge software to allow the scale to insert data into Access fom.  

Winwedge is calling a macro to run the following function:
Function GetWedgeData1()

' Declare variables
Dim ChannelNumber, MyData As Variant

' open a link to WinWedge
ChannelNumber = DDEInitiate("WinWedge", "Com1")
' get data from field(1)
MyData = DDERequest(ChannelNumber, "FIELD(1)")
' terminate the link
DDETerminate ChannelNumber
' if no data then quit
If Len(MyData) = 0 Then Exit Function

'Data from Field(1) in WinWedge is now in the variable "MyData". The following code sets
'the value of Textbox1 on Form1 to display the current WinWedge data.

Forms!T1DryweightForm!textbox1.Text = MyData

End Function

Here is the vb code that is run when data is placed in a textbox1 on my Access form:

Private Sub textbox1_AfterUpdate()
On Error GoTo ProcError

        Me![CavityDataSubfrm].Form!Wt = textbox1.Value
        DoCmd.GoToRecord , , acNext   ' Do Something for each record

 Exit Sub

 Select Case Err.Number
   Case 2105
     MsgBox "That's the last Cavity in the list.  Click Save Cavity Data button.", _
       vbInformation, "End of the list..."
   Case Else
     MsgBox "Error " & Err.Number & ": " & Err.Description, , _
       "Error in NextPP_Click procedure..."
 End Select
 Resume ExitProc

End Sub

Open in new window

The above code dumps data into the Wt field, then moves to the next record's wt field until it reaches the end and the message " That's the last Cavity in the list.  Click Save Cavity Data button." displays.  I need to dump data into a Wt field, and automatically move cursor to the right to dump data.  I need to move to insert data in 4 horizontal fields for one record and then go to the next record and insert 4 fields again til the end of the list.  Let me know if you need more info.  Any help would be greatly appreciated.
Inline Plastics I.T. Dept.
Watch Question

This is where the scale's code is populating the wt field.:

        Me![CavityDataSubfrm].Form!Wt = textbox1.Value

To populate four other fields on the subform you'd add lines like:

        Me![CavityDataSubfrm].Form!MyField1 = SomeValueFromSomewhere

(or textbox1.value if you want the same value that's going into wt)

Access isn't filling in the fields in the subform by position but by name (wt in the first case and my example of MyField1 in the second, so get the names of the fields you want to fill and stick them in there).
May be you can open table with Wt fiel as recordset, walk through records, get  WinWedge data and fill nesessary fields? Why you like to do it in form?


I am not familiar with recordsets in vba.  Can I send you files and explain more?
Inline Plastics
Yes. We can try to emulate your WinWedge


The form I am working on is Base Thickness. Select this menu option and then select an item code from the drop down and say yes or no to alternate specs question to get started. Then if you type in the textbox1 in the upper right of the form, the afterupdate event inserts the data to the T2CavityDataSubfrm's first field which is LeftTop and then goes to next record.  I want to insert data after each update of the textbox into LeftTop, then RightTop, LeftBottom, then RightBottom for the first record and then go to the next record and do the same and so on until the end of records.  Any help is greatly appreciated.
Test this sample. Button "Get WedgeData". Uncomment call to your function GetWedgeDAta1 in code and comment next string with dummy filling. TestWinWedge.mdb


Thank you als315.  I have to wrap my head around this and get back to you since I don't have it working yet.


Thankyou so much for all your help.  Right now, the first dump of winwedge or scale data goes into ALL the recordset fields.  How do I put the first Winwedge/scale data into the first field only?  Then, when the winwedge/scale data changes, put this data into the 2nd recordset field and so on until I read the EOF?  I understand if I am asking too much and if so, let me know.  I will accept your solution and ask as another question.  Thanks again for all your help..you got me closer than I was.
You should add some logic to VBA. I don't know how can you know when next data available. You can play with results of your Winwedge call. If it will be not possible, you can always add some msgbox with "Data ready?" and ok will mean - read data.