How Can I Access a 2nd Table from the same form

[Pampas58]

I'm almost finished and then...

I need to have a form that validates the user from a table. That works fine. I have front ends at 6 different computers that are linked to the database on the local server. I need to know who is logged into the system so I can do updates. I thought I'd put a tbl_local_user at each database, and from the Admin set up I would link to each table and I always know who is logged in.

I can't figure out how to write to a second table from the same form (Ignorant, I know...). I can use a query as a way to join both tables, but they don't have anything in common. I forced a join property, but I hope there is some syntax (like there was in DBIV) to open a table, delete the last record (when they log off), add a record when they log in.

Can you help me...

Thanks

Paul

 

[tviman]

Put a subform on your main form.

There's always a better way. The fun is trying to find it!

 

[willir]

Paul

You can also reference the form directly. This will be long, but I just have time to cut and paste here... Basically, in this case, I check to see if a form is open, and is correctly populated before writing to a table using the a value from the second form. This is an order entry system.

Function to yes / No to proceed...

Function CheckSisterFrm() As Boolean

'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 Funciton

Then, the following module when a record in a contineous form is double clicked event, if it passes the function call test, writes to the order detail table.

It does a bit of checking - it the item exists, it updates the order quantity; if not, it creates a new record. It then updates the second form which depicts the order detail plus calcuates the totla order...

Private Sub Form_DblClick(cancel As Integer)

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 CheckSisterFrm 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

I know this is not what you are trying to do, BUT, I think a similar approach may work. Obviously, you can not use an double-click event. (Double-click here so I know you are logged ;-) ) But there are other events that can be used to trigger the action.

Richard

 

[Pampas58]

Thank you very much!!!

I'll try it (learn from it, thus curing some personal ignorance) and I'll let you know how I do.

I owe you both!

Thanks!

Paul

 

[Pampas58]

Willir,

Thank you, it worked flawlessly.

Thanks,

Paul