Creating custom data view - help needed

[revmk]

Hello all,

** Think there must have been a lot of users on earlier, tried posting a few times but it just wouldn't post, hopefully I haven't ended up spamming the board! **

Sorry I have a bit of a problem which I just, for whatever reason, can't get my head around.

The setup…
> I'm using Access 2003 for reference all up to date etc.
> I have a client table, with a unique ID (tbl_client, seq_client_ref)
> I have a notes table, with a field related to the client ref and a field to hold a text 'note' about the client (seq_client_ref, t_client_note).

Clicking the ‘notes’ button on the client, opens the notes window. I want this window to be able to do two things. Display existing notes (which can not be edited) and allow entry of a new note for the client.

What I have tried….
1) Creating a sub report of the existing notes and passing the seq_client_ref to allow entry of a new note. This worked, but the sub form has one major flaw. The field size can not be controlled in any automatic way which meant either huge gaps in the view, missing data or a huge number of scroll bars. Not ideal.
2) Creating a report to view the notes and a form to enter them. This obviously works, but is a real pain as it breaks the ‘plan’ I have been given to have this all in one place for ease of use.
3) Creating a continuous form. Suffers from the same problem as (1).

There must be a solution to this. I have considered using a single field and finding someway to call and concatenate all the data into it – I’m just not sure how this could be done.

Can anyone suggest anything that might help?

Thanks in advance.

 

[Alisa]

Using a continuous form usually works for this sort of thing. Exactly what is the problem you are having? Maybe a screenshot would help?

 

[missinglinq]

Here's a hack that does exactly what you want. A second, unbound textbox (TempDataBox) is used to enter the data into. This textbox is originally hidden, and when the command button (IndirectDataInput) is clicked, it appears. Data is entered, and when the command button (now captioned “Commit Data”) is clicked again, the entered data is added to the memo field.

Now, in this example, there are two memo fields, but only one is bound, since the other memo field is simply a temporary holding area. The memo field is also locked so that all data entry has to be done thru the temporary textbox.

TempDataBox is unbound, and in the Property Box its Visible Property is set originally set to No. I place mine side by side with CommentsField so the user can refer to what's currently in the CommentsField section while entering new notes.

CommentsField is bound to the underlying table/query, and its Locked Property is set to Yes.

Place a command button on the form. Name it IndirectDataInput and in the Properties Box set its Caption to “Add New Data.”

Now use one of these two code examples, depending on whether or not you want a date/time stamp on each note:

‘IndirectEntry Without TimeStamp

Private Sub IndirectDataInput_Click()
If IndirectDataInput.Caption = "Add New Data" Then
   TempDataBox.Visible = True
   TempDataBox.SetFocus
   IndirectDataInput.Caption = "Commit Data"
Else
   IndirectDataInput.Caption = "Add New Data"
   If IsNull(Me.CommentsField) Then
      If Len(Me.TempDataBox) > 0 Then
        Me.CommentsField = Me.TempDataBox
        Me.TempDataBox = ""
        TempDataBox.Visible = False
      Else
        TempDataBox.Visible = False
      End If
    Else
      If Len(Me.TempDataBox) > 0 Then
       Me.CommentsField = Me.CommentsField & vbNewLine & Me.TempDataBox
       Me.TempDataBox = ""
       TempDataBox.Visible = False
      Else
       TempDataBox.Visible = False
      End If
      
    End If
End If
End Sub

‘IndirectDataEntry With TimeStamp

Private Sub IndirectDataInput_Click()
If IndirectDataInput.Caption = "Add New Data" Then
   TempDataBox.Visible = True
   TempDataBox.SetFocus
   IndirectDataInput.Caption = "Commit Data"
Else
   IndirectDataInput.Caption = "Add New Data"
   If IsNull(Me.CommentsField) Then
      If Len(Me.TempDataBox) > 0 Then
        Me.CommentsField = Now() & "  " & Me.TempDataBox
        Me.TempDataBox = ""
        TempDataBox.Visible = False
      Else
        TempDataBox.Visible = False
      End If
    Else
      If Len(Me.TempDataBox) > 0 Then
       Me.CommentsField = Me.CommentsField & vbNewLine & Now() & "  " & Me.TempDataBox
       Me.TempDataBox = ""
       TempDataBox.Visible = False
      Else
       TempDataBox.Visible = False
      End If
      
    End If
End If
End Sub

You will need to substitute the name of your textbox for CommenstField in the code.