TAKE CUSTOMER ID FROM ANOTHER OPEN FORM

[barry2004]

Hello guys, i have a customer Order database. My question is in regard to 2 of the forms, one called "Sender" the other called "Recipient".
SENDER FORM (fields)
------------------------------
Customer ID
Customer FirstName
Customer LastName
Address1
Address2
PostCode

RECIPIENT FORM (fields)
----------------------------
Sender ID (same as Customer ID on previous form - RELATED)
FirstName
LastName
Address1
Address2
Tel
-------------------------------------------------------
You would have to open the sender details form first to view/open recipient records relating to that sender or creating a new recipient related to an existing sender.
My question is this, when creating a new Recipient, i would like the Recipient form to take the "Customer ID" from the open record in the Sender form and display it in the "Sender ID" field on the Recipient form. The 2 fields are related to each other.

 

[willir]

Barry

You can reference the partner form directly. The following snippette of code is an example. Here, the end-user double clicks on an item in one form. If a customer is loaded in the second (order form), it creates an order for the selected item. In other words, not only does the Item form check the Order form, but it updates the Order form too. I suspect it is close enough to what you are trying to accomplish.

[COLOR=blue]
Private Sub Form_DblClick(cancel As Integer)[/color]

Dim dbs As DAO.Database, rst As DAO.Recordset, frm As Form
Dim lngCount As Long, strWhere As String, strSQL As String
Dim curItemPrice As Currency, curItemTotal As Currency

If [COLOR=blue]CheckSisterFrm[/color] Then

    Set frm = Forms!stOrderFrm

    strWhere = "[stOrderID] = " & Me.stOrderID & " and [stItemID] = " & Me.stItemID

    Set dbs = CurrentDb()

    lngCount = DCount("[ItemQty]", "stOrderDetailTbl", strWhere)

    If lngCount = 0 Then
    
        curItemPrice = DLookup("[ItemDefPrice]", "stItemTbl", "[stItemID] = " & Me.stItemID)
        curItemTotal = curItemPrice
        Set rst = dbs.OpenRecordset("stOrderDetailTbl")
        With rst
            .AddNew
            !stOrderID = Me.stOrderID
            !stItemID = Me.stItemID
            !ItemQty = 1
            !ItemPrice = curItemPrice
            !ItemTotal = curItemTotal
            .Update
        End With
    Else
        strSQL = "Select * from stOrderDetailTbl Where " & strWhere
        Set rst = dbs.OpenRecordset(strSQL)
        With rst
            .MoveFirst
            .Edit
            !ItemQty = !ItemQty + 1
            !ItemTotal = !ItemQty * !ItemPrice
            .Update
        End With
    End If
    
    frm!stItemDetailSbFrm.Requery
    frm!SalesTotal.Requery
    
End If

End Sub

[COLOR=blue]
Function CheckSisterFrm() As Boolean[/color]

'Before allowing function to work,
' - Sales order form must be open
' - Must have an assigned member
' - Must be in ADD or EDIT mode

Dim frm As Form
Dim booPass As Boolean
Dim strMsg As String, strForm As String

booPass = False
strForm = "stOrderFrm"

For Each frm In Forms

    If frm.Name = strForm Then
        If Nz(frm!stmemberID, 0) > 0 Then
            If Len(Nz(frm!PurchaseType, "")) > 0 Then
                If Nz(frm!stOrderID, 0) > 0 Then
                    If frm!CommitOrder = False Then
                        booPass = True
                        Me.stOrderID = frm!stOrderID
                    Else
                        strMsg = strMsg & "Order already committed" & vbCrLf
                    End If
                Else
                    strMsg = strMsg & "No sales order initialized" & vbCrLf
                End If
            Else
                strMsg = strMsg & "No purchase type selected" & vbCrLf
            End If
        Else
            strMsg = strMsg & "No member assigned for order" & vbCrLf
        End If
    End If

Next frm
    
If Not booPass Then
    If Len(strMsg) = 0 Then strMsg = "Sales Order From not open"
    MsgBox "Can not assign item to order !!" & vbCrLf & strMsg, vbOKOnly
End If
    
CheckSisterFrm = booPass

Me.SalesFormOpen = CheckSisterFrm

End Function

The CheckSisterFrm is a function used to make sure all the required data is available before creating the record -- bullet proofing.

Since this is part of an order entry system, the first bit of code checks to see if a record exists for the selected item -- if so, it edits / adjusts the count, if not it inserts a new record.

Richard

 

[barry2004]

Thanks will give it a go nd let you know.