Two tables, one form

[Remster]

Well, I've managed to cobble this together from various websites and, incredibly, it seems to cover points a and b (I say 'incredibly', because I have no real clue what I'm doing here):

Private Sub Button_Click()



Dim dbTestDatabase As DAO.Database
Dim rstTaskTable As DAO.Recordset
Dim intNextNum As Variant

Set dbTestDatabase = CurrentDb
Set rstTaskTable = dbTestDatabase.OpenRecordset("TaskTable")

intNextNum = DMax("[Task]", "TaskTable") + 1

rstTaskTable.AddNew
rstTaskTable("Task").Value = intNextNum
rstTaskTable.Update

End Sub

Now all I need to work out is how to start the number series from 11-01 on 1st January 2011. Any pointers would be much appreciated!

[RuralGuy]

Thank you.

Having thought about it some more, I suppose one thing that would be helpful is if I could have a button on my form that

a) added a new record to TaskTable

b) added the highest task number + 1 to the TaskNumber field

Does that sound feasible? (Even better would be if the series changed automatically from 10## to 11## on new year's day!)

All that you described can easily be accomplished with VBA behind a button.

[RuralGuy]

Now all I need to work out is how to start the number series from 11-01 on 1st January 2011. Any pointers would be much appreciated!

Right now I see no way for you to know what year the task sequence belongs to.

[Remster]

Never mind! With the guidance of this page I almost have what I wanted in the first place. Here's the code (see also the attachment):

Private Sub TaskID_NotInList(NewData As String, Response As Integer)
   On Error GoTo cboTaskID_NotInList_Err
   Dim intAnswer As Integer
   Dim strSQL As String
   intAnswer = MsgBox("The task " & Format(NewData, "00-00") & " is not currently listed." & vbCrLf & _
                      "Would you like to add it to the list now?" _
                      , vbQuestion + vbYesNo, "Task")
   If intAnswer = vbYes Then
      strSQL = "INSERT INTO TaskTable([TaskNumber]) " & _
               "VALUES ('" & NewData & "');"
      DoCmd.SetWarnings False
      DoCmd.RunSQL strSQL
      DoCmd.SetWarnings True
      MsgBox "The new task has been added to the list." _
             , vbInformation, "Task"
      Response = acDataErrAdded
   Else
      MsgBox "Please choose a task from the list." _
             , vbInformation, "Task"
      Response = acDataErrContinue
   End If
 
cboTaskID_NotInList_Exit:
   Exit Sub
 
cboTaskID_NotInList_Err:
   MsgBox Err.Description, vbCritical, "Error"
   Resume cboTaskID_NotInList_Exit
End Sub

I'm lacking only two things now:

1. I'm still getting the message 'The text you entered isn't an item in the list' even after it's been added. How do I either disable that message or enter the new value in the text box before the message has a chance to appear? According to the website I took the code from the line 'Response = acDataErrAdded' is supposed to handle that. I wonder if my input mask is causing some problems.

2. Because of the form's record source, Access won't allow me to move from one record to another unless a value is entered in DocumentTable.TaskID. I've found an ad hoc solution, which is to prefill a null TaskNumber in TaskTable and set the TaskID as the default value for DocumentTable.TaskID (record 16). But is there a better way to do this?

[RuralGuy]

I would start with the following changes to your code:

Private Sub TaskID_NotInList(NewData As String, Response As Integer)
   On Error GoTo cboTaskID_NotInList_Err
   Dim intAnswer As Integer
   Dim strSQL As String
   intAnswer = MsgBox("The task " & Format(NewData, "00-00") & " is not currently listed." & vbCrLf & _
                      "Would you like to add it to the list now?" _
                      , vbQuestion + vbYesNo, "Task")
   If intAnswer = vbYes Then
      strSQL = "INSERT INTO TaskTable([TaskNumber]) " & _
               "VALUES ('" & NewData & "');"
'      DoCmd.SetWarnings False
      CurrentDb.Execute strSQL, dbFailOnError
'      DoCmd.SetWarnings True
      MsgBox "The new task has been added to the list." _
             , vbInformation, "Task"
      Response = acDataErrAdded
   Else
      MsgBox "Please choose a task from the list." _
             , vbInformation, "Task"
      Response = acDataErrContinue
   End If
   
cboTaskID_NotInList_Exit:
   Exit Sub
   
cboTaskID_NotInList_Err:
   MsgBox Err.Description, vbCritical, "Error"
   Resume cboTaskID_NotInList_Exit
End Sub

[Remster]

I would start with the following changes to your code: ...

Thanks. I take it that's a technical improvement on what I already had rather than a solution to either of the problems I've listed.

[RuralGuy]

The Execute method does not issue warning but still errors when needed. Turning off warnings eliminates both warning and error messages. You may discover what is happening by using the Execute method. That makes it *more* that a technical change.

[Remster]

Oh, I see. Well, I've tried it and the outcome is the same!

[RuralGuy]

Okay, I looked at your last sample. In your case the NotInList would work if you switched the Bound column to 2 instead of 1. You then will probably have other problems.

[Remster]

Well, I've discovered that my first problem was due to my format and input mask settings, so I've removed them, changed the data type of TaskNumber to text, and added the validation rule 'Like "##-##"' to the table. So far so good. But when I add the same validation rule to the form, it doesn't recognise my entries as obeying it. Would that be because TaskNumber isn't the bound column? If so, is it possible to validate an unbound column?

[Remster]

Aha! I've cracked it: '[TaskNumber] Like "##-##"'.

The question now is how I get the validation rule to kick in first and then clear the field once the validation message has been OK'd. At the moment, if I enter invalid data, I'm getting my validation message, followed by my NotInList message, followed by 'The text you entered isn't an item in the list'. That would be extremely irritating for the user! Any guidance?

[Remster]

I'm moving this final issue to the programming section ('Cancelling the NotInList event').