Add a new "day number" record and increase the value by one

[mcpearce]

Hi All,



I have a table, tblEvents, which I use to store information about a meeting, training session or field trip, including the [StartDate]. I also have a table, tblEventDays, which I use to store the [DayNumber] 1, 2, 3... At the moment I have to enter the day numbers in the subform frmEventDays manually, but I would like to add button controls to increase/decrease the number of days for each event (i.e. add a new record where [DayNumber] is incremented by 1, and delete the last record if I have added too many day records).

I have added button controls to my EventDays subform and managed to use the macro builder to select and duplicate the last [DayNumber] record, but cannot figure out how to increase each successive [DayNumber] by 1.

I am not familiar with VBA code, so I couldn't really understand the answers on the similar threads in this forum. Anyone like to help me fill the blanks?

1. Add a day...
Option Compare Database
Private Sub AddDay_Click()

?

End Sub

2. Remove a day...

Option Compare Database
Private Sub RemoveLastDay_Click()

?

End Sub


Regards,

Mitch

P.S. I don't really have any access buddies, so if you could also recommend a good starting point for learning VBA, that would also be greatly appreciated.

[June7]

Are there other fields that require data entry?

One way to increment the day for each record is to set the DefaultValue property of the textbox. Set it to 1 in Design view then code in the BeforeUpdate event of textbox:

Me.textboxname.DefaultValue = Me.textboxname + 1

New record will be initiated by data entry into any other field and the default value will be applied.

As far as deleting a record, the keyboard delete will do that.

Learning resources:
http://office.microsoft.com/en-us/ac...010341717.aspx
Microsoft Access 2013 Programming by Example with VBA, XML, and ASP (Computer Science) by Julitta Korol (Mar 27, 2014)

[mcpearce]

Thanks for the speedy reply June7, esp. the learning resources - will take a look.

There are no other fields requiring entry, at least not initially...



The image (hopefully you can see it) should illustrate what I'm trying to do.

I'd like the use to be able to click the up arrow button control to add a new tblEventDay record, where [DayNumber] = 6, then 7, then 8...
I'd like the user to be able to click the down arrow button to delete the largest [DayNumber] record for the Event...

Having records for each EventDay like this has been necessary for me to be able to create a crosstab query displaying events in a 'calendar' type format.

Any other suggestions?

[June7]

Use code to run SQL INSERT and DELETE actions.

CurrentDb.Execute "INSERT INTO tablename(EventID, DayNumber) VALUES(" & Me.EventID & ", " & Me.textbox + 1 & ")"
Me.textbox = Me.textbox + 1

CurrentDb.Execute "DELETE FROM tablename WERE EventID = " & Me.EventID & " AND DayNumber=" & Me.textbox
Me.textbox = Me.textbox - 1

Run Me.Requery after each.

Otherwise, if want to just store the start and end dates of the event, review https://www.accessforums.net/databas...ses-18459.html

[mcpearce]

Hi June7,

Brilliant! Took a bit of messing around, but I figured out how to transfer the macros to VBA code, and then insert your recommended code plus a little more I found and I ended up with...

Private Sub Command47_Click()
On Error GoTo Command47_Click_Err
On Error Resume Next
CurrentDb.Execute "INSERT INTO tblEventDays (EventID, DayNumber) VALUES (" & Me.EventID & ", " & Me.TotalDays + 1 & ")"
If (MacroError <> 0) Then
Beep
MsgBox MacroError.Description, vbOKOnly, ""
End If
Dim f As Form
For Each f In Access.Forms
f.Requery
Next
Command47_Click_Exit:
Exit Sub
Command47_Click_Err:
MsgBox Error$
Resume Command47_Click_Exit
End Sub

Works a charm! : )

I also ordered the book you recommended! : )

Regards,

Mitch