BackEnd MasterData Updating without any forms

[sbbmaster09]

I have 2 tables, a table for attendance and table for master data. I will be entering a status from a start date and end date in the attendance. Then in the back end, the master data will update. During the start date up to the end date of the attendance, one field in the roster will change to inactive. Once the end date has already passed it will changed back to active. It should not use any forms. It should automatically run.

Enlighten me on what I can do to possible have this functions, someone said scripting but I am not much familiar with it. Please send me a link or any reference that can help me about this.

Thank you.

[ranman256]

macro.
run the macro , to run your queries for updates.

[SamT]

I will be entering a status from a start date and end date in the attendance.
Is the Attendance in Excel or Word?

[sbbmaster09]

macro.
run the macro , to run your queries for updates.

Hi ranman256,

How can I ran a query using a macro? Step by step please so I can learn from it. Thank you.

[sbbmaster09]

For future reference:

I am able to solve the problem,

[1] I create Queries that is links from the 2 tables so they can filter enough the data I need,
[2] Created a continuous form where the query I make is being pulled out and save the data to update the main database, code below:

Private Sub Form_Load()

On Error GoTo Err_Handler




Dim dbforms As DAO.Database
Dim rstforms As DAO.Recordset
Dim strSQL As String


Set dbforms = CurrentDb
strSQL = "SELECT * FROM MainData WHERE [Enterprise ID] = '" & Me.txtAttUser.Value & "'"


Set rstforms = dbforms.OpenRecordset(strSQL, dbOpenDynaset)

rstforms.Edit
rstforms("Employment Status").Value = DLookup("[EmpStatus]", "qryInactivateEmpStat", "[Username] = '" & Me.txtAttUser.Value & "'")

rstforms.Update

rstforms.Close
dbforms.Close

Me.Requery

DoCmd.Close






Exit_Handler:
Exit Sub


Err_Handler:
MsgBox Err.Description
Resume Exit_Handler

End Sub

[3] Created a macro to run the the continuous form hidden,
> Macro > Open Form > Form Name > View Form > Data Mode: Edit > Window Mode: Hidden

[4] Then call the code below to open with the form load on the main form

Sub ExecuteUpdates() Do While DCount("[Username]", "qryInactivateEmpStat", "[EmpStatus] = '" & "Inactive" & "'") <> 0
DoCmd.RunMacro "RunInactive"
Loop

End Sub

[5] So everytime I open my form it automatically updates, if you have any ideas shorter than this just let us know.


Thank you! :)

[SamT]

Thank you for sharing your solution. I am sure it will help someone in the future.