On Nov 6, 9:08*am, Russman wrote:
On Nov 5, 2:02*pm, "Dirk Goldgar"
wrote:
"trying2learn" wrote in message
...
I have a form with an unbound listbox (0) set to simple for multi select,
that I populate it from a table that contains boardmembers. the query has
2
fields the boardmemberid and a concatenated field of
Boardmember:bmlastname &
" " & bmfirstname. The listbox column count is set to 2 and they are
0",1.5".
The form also has an unbound textbox (name dBdate) set to date() and a
combo
box (named cboBoard) that when clicked on has 3 options: board 1, board 2,
Joint.
I would like to get the date, selected data from conbobox and selected
data
from the list box to append to a table called tblBmeetings which has the
following fields.
bmeetingid(auto pk)
bmid *(number(fK to bmid in tblboardmembers))
Board
BoardMember
Bdate
I would like to do this with a cmd button.
If the field BoardMember in tblBMeetings is the calculated Boardmember field
from your combo box's rowsource query, I wouldn't have that field in the
table because it is redundant; *the BMID field identifies the board member,
and the board member's name can always be derived by lookup and calculation
from the BoardMembers table. *However, I'll go ahead with this as you
originally requested it.
Here's possible code for your command button, which I'm calling
"cmdAddMeeting". *I'm assuming that the list box is named "lstBoardMembers".
Warning: this is totally air code:
'------ start of suggested code ------
Private Sub cmdAddMeeting_Click()
* * Dim db As DAO.Database
* * Dim varItem As Variant
* * ' Verify required information.
* * If Me.lstBoardMembers.ItemsSelected.Count = 0 Then
* * * * MsgBox "You have to choose at least one board member!"
* * * * Me.lstBoardMembers.SetFocus
* * * * Exit Sub
* * End If
* * If IsNull(Me.dBdate) Then
* * * * MsgBox "You have to enter a date!"
* * * * Me.dBdate.SetFocus
* * * * Exit Sub
* * End If
* * If IsNull(Me.cboBoard) Then
* * * * MsgBox "You have to choose a board"
* * * * Me.cboBoard.SetFocus
* * * * Exit Sub
* * End If
* * ' We have what we need, so add the meeting(s).
* * Set db = CurrentDb
* * With Me.lstBoardMembers
* * * * For Each varItem In .ItemsSelected
* * * * * * db.Execute _
* * * * * * * * "INSERT INTO tblBmeetings " & _
* * * * * * * * * * "(BMID, Board, BoardMember, Bdate) " & _
* * * * * * * * * * "VALUES (" & _
* * * * * * * * * * * * .ItemData(varItem) & ", " & _
* * * * * * * * * * * * Chr(34) & Me.cboBoard & Chr(34) & ", " & _
* * * * * * * * * * * * Chr(34) & .Column(1, varItem) & Chr(34) & ", " & _
* * * * * * * * * * * * Format(dBdate, "\#mm\/dd\/yyyy\#") & ")", _
* * * * * * * * dbFailOnError
* * * * Next varItem
* * End With
End Sub
'------ end of suggested code ------
The above code assumes that BMID is a numer field, while Board and
BoardMember are text, and Bdate is a date field.
--
Dirk Goldgar, MS Access MVP
Access tips:www.datagnostics.com/tips.html
(please reply to the newsgroup)- Hide quoted text -
- Show quoted text -
Thank you for the above code. I was not even at the point to do the
validation, was trying to get it to work-so thank you very much for
that.
The validation works fine but when I hit the submit button I get the
following error;
SynTax Error (missing operator) in query expression " Tom Slone".
Tom is the board member name. Would it have an effect if in
lstBoardmembers I am populating it in this way.
In the query *I am using the tblBoardmembers. In stead of dropping
down bmlastname and bmfirstname as the fields. I have this as the row
source:
SELECT [bmlastname] & " " & [bmfirstname] AS BoardMember FROM
BoardMembers;
The bound column is 1 and column count is 1 with width set to 1.5
Again it populates fine.
Thanks for your assistance- Hide quoted text -
- Show quoted text -
Sorry- I AM AN IDIOT-
After I posted the oringinal request last night I was still messing
with it and forgot that I had changed the column count from 2 to 1. I
then realized that after I sent the second post and changed it back.
Your code works PERFECTLY.
Thanks so much again for adding the validation code-saved me a few
steps.
Try2Learn