Complex field updating involving VBA

[marcin2k]

I have two tables with a one to many relationship

Table1: OrderNum Table2: OrderNum
ItemNum ItemNum
RackDesc RackDesc

Table2 is populated automatically when someone scans a barcode for an order and a rack. I need to move that information to Table1 which is the main database with all the order information.
The probl0em is here: Table1 only has one record per ItemNum, while table2 can have multiple rackdesc per itemnum. I need to make it so that if there is only one record in Table2 for a given order and item number then copy that rackdesc to table 1. But if there is multiple rack desc in table 2 then when copying to table 1 put it in the following syntax: "RackNum1, RackNum2,..etc" for as many records there is for a give order and item num in table2.

Not sure if this is possible, currently I just have an update query which copys the first of all the rackdesc in case there are multiple.

Thanks, for any ideas

 

[marcin2k]

Solved the issue, here is the function I put into an update query for that given field:

Function UpdateRack(intOrder As Integer, intItemNum As Integer) As String
Dim db As Database
Dim rst As Recordset
Dim strRack As String
strRack = ""
Set db = CurrentDb
Set rst = db.OpenRecordset("Select tRack.OrderNum, tRack.ItemNum, tRack.RackDesc From tRack Where tRack.OrderNum = " & intOrder & " And tRack.ItemNum = " & intItemNum & ";")

With rst
.MoveFirst
Do Until rst.EOF
strRack = strRack & !RackDesc & ","
.MoveNext
Loop
End With
If Right(strRack, 1) = "," Then
strRack = Left(strRack, Len(strRack) - 1)
End If
UpdateRack = strRack
End Function

 

[gillrowley]

How about something like this: Assume you have a form with two text boxes bound to Table1

Private Sub UpateTable1()
Dim cn as ADODB.Connection
Dim rs2 as ADODB.Recordset
Dim sql as String, sql2 as String
dim strRack as String

Set cn = CurrentProject.Connection
Set rs2 = New ADODB.Connection

sql = "Select * From Table2 where OrderNum = " & txtOrderNum & " And ItemNum = " & txtItemNum"

sql2 = "UPDATE Table1 SET RackDesc = "strRack"
WHERE (((OrderNum)= " & txtorderNum & ") AND ((ItemNum)=" & txtItemNum & "A));"


strRack = ""

rs2.Open "sql",cn,Openkeyset,LockOptimistic
With rs2
If Not (.BOF and .EOF) Then
.MoveFirst
Do Until .EOF
strRack = strRack + rs2("RackDesc") + " "
.MoveNext
Loop
End If
.Close
End With

With cn
.Execute sql2
End With

Set rs2 = Nothing
Set cn = Nothing

End Sub

This takes all the records in table 2 and creates one long string of all the different Rack Descriptions and the puts that string into the correct record in table 1. You should make sure your field size can accomodate the largest possible string you think you'll need.

 

[gillrowley]

Apologies - should read -

Set rs2 = New ADODB.Recordset, not ADODB.Connection.