Add Records to Table according to selection of a field from a form. (1 Viewer)

angekap

New member
Local time
Today, 05:05
Joined
Mar 16, 2021
Messages
12
Hi Guys,

I am trying to add records from a form which is based on a Table named DAYUSE and at the same time every time I add a record to that table specific data is added to another Table called RECEPTION but that is based on a criteria from a combo box named settlement. I've tried to modify the code but I must have got it wrong somewhere maybe an extra End If or I have to leave out the extra line "ElseIf Me![SETTLEMENT] = "VIVA" Then"

The VBA runs on exit after the last field AMOUNT. Can anyone help me Thanks !!!

Please use CODE TAGS!!!!

Code:
Private Sub AMOUNT_Exit(Cancel As Integer)
Dim Msg, Style, Title, Response, MyString
Msg = "CONFIRM TRANSACTION YES to Continue NO to Change/Edit Transaction ?"   ' Define message.
Style = vbYesNo + vbInformation + vbDefaultButton1 ' Define buttons.
Title = "FINALIZE SECURITY BOX ENTRY"  ' Define title.

Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then  ' User chose Yes.

Dim MyDB As Database, Myset As Recordset
Set MyDB = CurrentDb()
Set Myset = MyDB.OpenRecordset("RECEPTION", dbOpenTable)
If Me![SETTLEMENT] = "CASH" Then
Myset.AddNew
Myset![DEPARTMENT] = Me![DEPT]
Myset![USER] = Me![USER]
Myset![TYPE] = Me![ROOMNO]
Myset![DATE] = Me![DATEDAY]
Myset![IN] = Me![AMOUNT]
Myset.Update

ElseIf Me![SETTLEMENT] = "VIVA" Then

Myset.AddNew
Myset![DEPARTMENT] = Me![DEPT]
Myset![USER] = Me![USER]
Myset![TYPE] = Me![ROOMNO]
Myset![DATE] = Me![DATEDAY]
Myset![IN] = Me![AMOUNT]
Myset.Update
Myset.Close

ElseIf Me![SETTLEMENT] = "VIVA" Then

Myset.AddNew
Myset![DEPARTMENT] = "MONEY IN/OUT"
Myset![USER] = Me![USER]
Myset![TYPE] = "DAY USE CREDIT CARD VIVA"
Myset![DATE] = Me![DATEDAY]
Myset![OUT] = Me![AMOUNT]
Myset.Update
Myset.Close
End If

DoCmd.GoToRecord acDataForm, "DAYUSE", acNewRec
DoCmd.GoToControl "USER"


MyString = "Yes"    ' Perform some action.

    DoCmd.GoToControl "NAME"
    
    MyString = "No" ' Perform some action.
End If

End Sub
 
Last edited by a moderator:

Ranman256

Well-known member
Local time
Yesterday, 23:05
Joined
Apr 9, 2015
Messages
4,336
Normally you dont write code to add records, you just :
open the form, which is bound to the table , and enter data.

It saves automatically.
 

isladogs

MVP / VIP
Local time
Today, 03:05
Joined
Jan 14, 2017
Messages
18,164
Agree with Ranman. Avoid using recordsets where these aren't needed. Use bound forms instead.

However you have repeated your ElseIf Me!Settlement ="VIVA" Then line.
The second of those blocks will never run.
It should just be one word: Else
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 23:05
Joined
Feb 19, 2002
Messages
42,872
ElseIf Me![SETTLEMENT] = "VIVA" Then
Why do you have two of these?
If you are adding rows to a dependent table, you CANNOT do it until the record on the main form has been saved. Also, due to the event you have used, you could insert duplicate rows. The Best event to use is the Form"s AfterUpdate event. That way you know the main record has been saved and there will be now changes.

I agree with the others. Also, from the looks of what you are doing, you are saving data in multiple places.

Name is a poor choice for an object name since every object has a Name property and there are situations where Access won't know which you are referring to.
 

Users who are viewing this thread

Top Bottom