Form to expedite entry...is this possible?

[Guest]

I have a table, tFits, whose primary key is "StyleID" (text field).
This is a one-to-many relationship with table tComments, which has

"CommentID" primary key Autonumber
"StyleID" foreign key Text
"Comment" memo field
"CommentDate" Date field

Usually, any particular comment will apply to more than one Style, maybe 5 -
20. But these styles don't necessarily have anything in common that can be
designated as criteria for an append query.

I am wondering if there is a way to have a form that has 1 memo field, one
date field, and maybe 20 text boxes to enter style numbers that a new
comment record will be created for with the click of a command button.

Any ideas as to how to approach this, if possible?

Also, if you have time, please take a look at my other post which remains
unanswered.
http://www.microsoft.com/office/com...ffice-access&lang=en&cr=US&sloc=en-us&m=1&p=1

Thank you.

 

[Dirk Goldgar]

I have a table, tFits, whose primary key is "StyleID" (text field).
This is a one-to-many relationship with table tComments, which has

"CommentID" primary key Autonumber
"StyleID" foreign key Text
"Comment" memo field
"CommentDate" Date field

Usually, any particular comment will apply to more than one Style,
maybe 5 -
20. But these styles don't necessarily have anything in common that
can be designated as criteria for an append query.

I am wondering if there is a way to have a form that has 1 memo
field, one date field, and maybe 20 text boxes to enter style
numbers that a new comment record will be created for with the click
of a command button.

Any ideas as to how to approach this, if possible?

Also, if you have time, please take a look at my other post which
remains unanswered.
http://www.microsoft.com/office/com...ffice-access&lang=en&cr=US&sloc=en-us&m=1&p=1

Thank you.

Easy enough, with an unbound form and a little code. Suppose your form
has these text boxes:

txtComment
txtCommentDate
txtStyle1
txtStyle2
txtStyle3
txtStyle4
txtStyle5
...
txtStyle19
txtStyle20

That is, 20 text boxes all named with the base name "txtStyle", but with
a numeric suffix from 1 to 20.

Now your button's Click event procedure can use code to open a recordset
on tComments, loop through the "txtStyle" text boxes, and add a record
to tComments for each text box that is not Null:

'----- start of example code -----
Private Sub cmdAddComment_Click()

Dim rs As DAO.Recordset
Dim sStyle As String
Dim i As Integer

If IsNull(Me!txtComment) Then
MsgBox "You have to enter a comment!"
Exit Sub
End If

Set rs = CurrentDb.OpenRecordset( _
"SELECT * FROM tComments WHERE False")

With rs

For i = 1 to 20

sStyle = Me.Controls("txtStyle" & i) & vbNullString

If Len(sStyle) > 0 Then
.AddNew
!StyleID = sStyle
!Comment = Me!txtComment
!CommentDate = Me!txtCommentDate
.Update
End If

Next i

.Close

End With

Set rs = Nothing

End Sub
'----- end of example code -----

 

[Guest]

This is fantastic, Dirk.

Thank you very much! It works GREAT. Have a wonderful week.