By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,680 Members | 1,508 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,680 IT Pros & Developers. It's quick & easy.

Set defauts dates for records in bound datasheet subform using vba

P: 1
Hi everyone!
I'm working on a booking form in access 2010 linked to a database (Db_booking). I've 2 tables: partecipanti and pasti (1-n relation).
In my main form "iscrizione", I've a bound datasheet subform "pasti" with fields: id_pasto (auto inc), giorno_pasto (date), colazione (checkbox), pranzo(checkbox), cena (checkbox).

What I want to do is:
While starting to fill the client ID, I want to fill the subform with ALL the dates of the meal in order to just check the meals. Cause when the client stays for many days, it's tiring to always have to go to the calendar to choose the right date.
I tried with RECORDSET and SETFOCUS (ERROR 2110...) without any success. Here is a sample of what I tried:

Expand|Select|Wrap|Line Numbers
  1.  
  2.  
  3. Dim Rs As DAO.Recordset
  4. > Set Rs = Me!pasti.Form.Recordset
  5. > Rs.MoveFirst
  6. > For i = 1 to 14
  7.     Me.giorno_pasto = Date( i&08.2015)
  8.     DoCmd.RunCommand acCmdRecordsGoToNew
  9. Next i
  10. > Set Rs = Nothing
  11.  
  12. ------------------------------------------------
  13. Me.pasti.SetFocus
  14. DoCmd.RunCommand acCmdRecordsGoToNew
  15. Me.giorno_pasto =   Date (31.07.2015)
  16. For i = 1 to 14
  17.     Me.giorno_pasto = Date( i & 08.2015)
  18.     DoCmd.RunCommand acCmdRecordsGoToNew
  19. Next i
  20. ------------------------------------------------
  21.  
  22. 'pasti.SetFocus
  23. 'Forms!pasti.SetFocus
  24. 'Me.pasti.Form.SetFocus
  25.  
  26. DoCmd.GoToRecord acDataForm, Me.Controls.Item, acNewRec
  27. DoCmd.SelectObject acForm, pasti, False
  28.  
  29. DoCmd.RunCommand acCmdRecordsGoToNew
  30. Me![pasti].[Data del pasto] = "31.07.2015"
  31.  
  32.  
  33. For i = 1 To 14
  34.     Me![pasti].[Data del pasto] = i & ".07.2015"
  35.     DoCmd.RunCommand acCmdRecordsGoToNew
  36. Next i
  37.  
As a vba beginner, I really need help. Is it possible to implement what I need? What do i've to try?
Thx for your attention and answers
Jul 22 '15 #1
Share this Question
Share on Google+
1 Reply


jforbes
Expert 100+
P: 1,107
Using the UI to insert multiple records is not the best of ideas as sooner or later it will either break or do something that you never expected.

I would recommend creating some SQL to insert your Records. I mocked this up to show a way to do this:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command8_Click()
  2.  
  3.     Dim dStart As Date
  4.     Dim dEnd As Date
  5.     Dim dDateCounter As Date
  6.     Dim sSQL As String
  7.  
  8.     dStart = Now()
  9.     dEnd = DateAdd("d", 5, Now())
  10.  
  11.     For dDateCounter = dStart To dEnd
  12.         sSQL = ""
  13.         sSQL = sSQL & "INSERT INTO DateTimes (CompanyID, ScheduleDate)"
  14.         sSQL = sSQL & "VALUES ('" & Me!CompanyID & "', #" & Format(dDateCounter, "mm/dd/yyyy") & "#)"
  15.         CurrentDb.Execute (sSQL)
  16.     Next dDateCounter
  17.     Me.DateTimesDatasheet.Requery
  18.  
  19. End Sub
This code has a Main Form with companies on it, and a Sub Form with items with dates on it. The code Loops from the a Start date of Now(), to five days from Now() and inserts a record in a Sub Table for each day of the loop. It then does a Requery on the SubForm so that the newly inserted records are displayed.

For your case, you would probably want a couple Fields on the Form to pick the Start and Stop Dates. You may also need to check whether or not a Date Record already exists, and what to do in that case.
Jul 23 '15 #2

Post your reply

Sign in to post your reply or Sign up for a free account.