Inserting Values in tables

[aamudog]

I am trying to refer to a field in another table but I don't know the correct syntax. Can someone please help me out? I am new to this so I really dont know what to do.

I have two tables :
MemberTbl
MemberNum(PK)
SignInTbl
TimeID (PK)
MemberNum
TimeIn
NumGuest
MemFName
MemLName
dtmStamp
NumGuest



I have a sign in form which has a MemberNum and NumGuest control. I want to add a command button that allows the user to click it and then receive a msg that tells them they have successfully signed in and gives the MemberName and then update the tables. Can someone tell me how to update the tables with the data entered. It's not updating the time in the Member table. This is what I have so far:


Private Sub SignIn_Cmd_Click()
If Me.Form.Dirty Then
If MsgBox("Do You Want to Sign In?", vbYesNo + vbQuestion, "Sign In?") = vbYes Then

MsgBox DLookup("[MemFName]", "MemberTbl", Me.MemberNum) & " has been Signed In", vbOKOnly + vbInformation, "SUCCESSFULLY SIGNED IN"
DoCmd.RunCommand Command:=acCmdSaveRecord
DoCmd.GoToRecord , , acNewRec 'Add New Record
Else
Me.Form.Undo
End If
End If
End Sub

 

[Remou]

Access automatically saves when a form is bound to a table or query, it is very rarely necessary to save. I can see no reason at all to go to a new record on a sign in form. You may wish to reconsider the design of the SignInTbl, as you appear to have both duplicate and redundant fields:

http://www.r937.com/relational.html

You can set the default value of a field to Now(), which will insert date and time into a new record.

 

[JoeAtWork]

MsgBox DLookup("[MemFName]", "MemberTbl", Me.MemberNum) & " has been Signed In", vbOKOnly + vbInformation, "SUCCESSFULLY SIGNED IN"

Your code assumes the person will always enter a valid MemberNum. What if they don't? Answer - you will get an error because DLookup will return a Null value.

I would separate the DLookup from the MsgBox. First do the DLookup (wrapped in Nz in case the MemberNum is invalid). If it passes that test, then you can save the SignIn record and tell the user they were successful.

To elaborate on what Remou said, I think you should move the MemFName and MemLName fields to the MemberTbl table (assuming the MemberNum is always the same person - if not I can't conceive how the MemberNum would be useful).

As for your question about updating the time field (and you didn't specify exactly which field that is), do you have a default value set up for it (I assume it would be Now())?

 

[TheAceMan1]

How are ya aamudog . . .

Your tables are not normalized (see Normalizing Tables). They should look more like:

[blue][purple]MemberTbl[/purple]
MemberNum(PK)
MemFName
MemLName

[purple]SignInTbl[/purple]
TimeID (PK)
MemberNum
dtmStamp
NumGuest[/blue]

Note I removed [blue]Time In[/blue]. This field is inclusive in your date/time stamp [blue]dtmStamp[/blue] and [blue]dtmStamp[/blue] should update from the code in your button (before saving) with:

[blue]   Me.dtmStamp = Now()[/blue]

For updating as you asked, have a look at [blue]Update or Append Queries[/blue] . . .

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[aamudog]

I have updated my tables so that they are normalized. This is what I have.

MemberTbl
MemberNum(PK)
MemFName
MemLName

SignInTbl
TimeID (PK)
MemberNum
dtmStamp
NumGuest



I have the following code for my command button. What I would like to have happen is when I click the Sign in command button I want the user to see a message that asks if they want to sign in if they click yes then I want the code to look up the member's name based on the member number they entered and return a message that they have been signed in. Right now when I click the command button it only returns the member name for the first record in the Member table no matter what Member number is entered on the form. Can someone please tell me what Im doing wrong and how I can correct this.

Private Sub CmdSignIn_Click()

On Error GoTo Err_CmdSignIn_Click

    If Me.Form.Dirty Then
        If MsgBox("Do You Want to Sign In?", vbYesNo + vbQuestion, "Sign In?") = vbYes Then
        
            MsgBox DLookup("MemFName", "TblMember", Me.MemberNum) & " has been Signed In", vbOKOnly + vbInformation, "SUCCESSFULLY SIGNED IN"
        Else
            Me.Form.Undo
        End If
    End If

    
    DoCmd.GoToRecord , , acNewRec

Exit_CmdSignIn_Click:
    Exit Sub

Err_CmdSignIn_Click:
    MsgBox Err.Description
    Resume Exit_CmdSignIn_Click
    
End Sub

 

[PHV]

Replace this:
DLookup("MemFName", "TblMember", Me.MemberNum)
with this:
DLookup("MemFName", "TblMember", "MemberNum=" & Me.MemberNum)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[aamudog]

When I use this I get [highlight #FF99FF]"data type mismatch in critera expression"[/highlight].

DLookup("MemFName", "TblMember", "MemberNum=" & Me.MemberNum)

This is how my tables are setup.
MemberTbl
MemberNum(PK) (text field)
MemFName (text field)
MemLName (text field)

SignInTbl
TimeID (PK) (AutoNumber)
MemberNum (text field)
dtmStamp (Date/Time)
NumGuest (Number)

 

[PHV]

So, try this:

DLookup("MemFName", "TblMember", "MemberNum='" & Me.MemberNum & "'")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[aamudog]

That works like a charm. THANK YOU, THANK YOU, THANK YOU!

 

[aamudog]

Can someone help me edit my code to check for errors if the user enters a member number other than one that is stored in the member table? I have no clue how to do this.

 

[aamudog]

Ok. This is what I have so far. It will give me an error message when the user enters the wrong member number but now it wont give me the message when the correct member number is entered. I know I am probably missing an Else statement but I'm not sure where to place it. Any suggestions???

Private Sub CmdSignIn_Click()
On Error GoTo Err_CmdSignIn_Click

    If Me.Form.Dirty Then
      If IsNull(DLookup("MemFName", "TblMember", "MemberNum='" & Me.MemberNum & "'")) Then
        MsgBox "Member Number is not valid, Please enter a valid Member Number.", vbOKOnly, "Invalid Entry..."
        DoCmd.CancelEvent
       End If
    Else
       If MsgBox("Do You Want to Sign In?", vbYesNo + vbQuestion, "Sign In?") = vbYes Then
         MsgBox DLookup("MemFName", "TblMember", "MemberNum='" & Me.MemberNum & "'") & " has been Signed In", vbOKOnly + vbInformation, "SUCCESSFULLY SIGNED IN"
       Else
         Me.Form.Undo
       End If
    End If
    
    DoCmd.GoToRecord , , acNewRec

Exit_CmdSignIn_Click:
    Exit Sub

Err_CmdSignIn_Click:
    MsgBox Err.Description
    Resume Exit_CmdSignIn_Click
    
End Sub