Input from Form, Create fields for Query which joins them with lis

[Guest]

Using Access 2007, I am creating a temporary table [ActivityTrker] which will
feed a master table [MeetingMaster]. The reason for this is because the user
wants to do "bulk" selection and not have to do one person at a time; plus,
the meetings can be one of 30 types, and they can take place any time, any
day of the week; thus, no preloading a table with Meeting date/time and just
asking for attendance.

I have searched this and other forums, asked for help once before, have
researced multiple books/web sites, and not really found any help so far.
Right now, I am not able to get the query to accept the fields coming from
the form as valid fields.

What results from the following code is that the query wants to treat the
input fields as parameters and wants me to re-enter the information, which,
due to the need of having the combo box "comboTypeContact," this is
unacceptable, unless someone knows how to make a combo box a parameter. I am
sure it has been done, but I sure don't know how to do it.

Any help greatly appreciated.

fs

Please note the followings code:

Public Sub BulkMMcmd_Click()

Dim strTblBldqry As String
Dim typeofcontact As String
Dim activitydate As Date
Dim dbs As Database
Dim mintimecredit As Integer
Dim rsTbl As Recordset
Dim attended As CheckBox
Dim tkrname As String

tkrname = "ActivityTrker"
typeofcontact = Me.comboTypeContact.Value
activitydate = Me.dateActivity.Value
mintimecredit = Me.TimeCredit.Value

'Due to the wrapping in the Message box, this string looks like it lacks
ending
'line identifiers; however, the "typeofcontact" line is part of the first
line. fs
strTblBldqry = "SELECT DISTINCT L8Names.stu_name, L8Names.stu_id,
typeofcontact, activitydate, " & _
"mintimecredit, L8Names.attended INTO ActivityTrker " & _
"FROM L8Names;"


DoCmd.RunSQL strTblBldqry
DoCmd.OpenTable tkrname, acViewNormal
DoCmd.Save acTable, tkrname
DoCmd.Close acTable, tkrname

End Sub

 

[OldPro]

Using Access 2007, I am creating a temporary table [ActivityTrker] which will
feed a master table [MeetingMaster]. The reason for this is because the user
wants to do "bulk" selection and not have to do one person at a time; plus,
the meetings can be one of 30 types, and they can take place any time, any
day of the week; thus, no preloading a table with Meeting date/time and just
asking for attendance.

I have searched this and other forums, asked for help once before, have
researced multiple books/web sites, and not really found any help so far.
Right now, I am not able to get the query to accept the fields coming from
the form as valid fields.

What results from the following code is that the query wants to treat the
input fields as parameters and wants me to re-enter the information, which,
due to the need of having the combo box "comboTypeContact," this is
unacceptable, unless someone knows how to make a combo box a parameter. I am
sure it has been done, but I sure don't know how to do it.

Any help greatly appreciated.

fs

Please note the followings code:

Public Sub BulkMMcmd_Click()

Dim strTblBldqry As String
Dim typeofcontact As String
Dim activitydate As Date
Dim dbs As Database
Dim mintimecredit As Integer
Dim rsTbl As Recordset
Dim attended As CheckBox
Dim tkrname As String

tkrname = "ActivityTrker"
typeofcontact = Me.comboTypeContact.Value
activitydate = Me.dateActivity.Value
mintimecredit = Me.TimeCredit.Value

'Due to the wrapping in the Message box, this string looks like it lacks
ending
'line identifiers; however, the "typeofcontact" line is part of the first
line. fs
strTblBldqry = "SELECT DISTINCT L8Names.stu_name, L8Names.stu_id,
typeofcontact, activitydate, " & _
"mintimecredit, L8Names.attended INTO ActivityTrker " & _
"FROM L8Names;"

DoCmd.RunSQL strTblBldqry
DoCmd.OpenTable tkrname, acViewNormal
DoCmd.Save acTable, tkrname
DoCmd.Close acTable, tkrname

End Sub

Usually, this only happens if Access can't find the fields in the
table. What is your table structure?

 

[Guest]

I am trying to take a table [L8Names], which has all of the names that could
be selected as having attended, and it has the following fields: stu_id,
stu_name, attended (which is just a null check box). I am trying to add to
each row of this table then the fields from the unbounded form.

The end results is suppose to be my temporary selection table, which would
then be appended into the master table.

fs

Using Access 2007, I am creating a temporary table [ActivityTrker] which will
feed a master table [MeetingMaster]. The reason for this is because the user
wants to do "bulk" selection and not have to do one person at a time; plus,
the meetings can be one of 30 types, and they can take place any time, any
day of the week; thus, no preloading a table with Meeting date/time and just
asking for attendance.

I have searched this and other forums, asked for help once before, have
researced multiple books/web sites, and not really found any help so far.
Right now, I am not able to get the query to accept the fields coming from
the form as valid fields.

What results from the following code is that the query wants to treat the
input fields as parameters and wants me to re-enter the information, which,
due to the need of having the combo box "comboTypeContact," this is
unacceptable, unless someone knows how to make a combo box a parameter. I am
sure it has been done, but I sure don't know how to do it.

Any help greatly appreciated.

fs

Please note the followings code:

Public Sub BulkMMcmd_Click()

Dim strTblBldqry As String
Dim typeofcontact As String
Dim activitydate As Date
Dim dbs As Database
Dim mintimecredit As Integer
Dim rsTbl As Recordset
Dim attended As CheckBox
Dim tkrname As String

tkrname = "ActivityTrker"
typeofcontact = Me.comboTypeContact.Value
activitydate = Me.dateActivity.Value
mintimecredit = Me.TimeCredit.Value

'Due to the wrapping in the Message box, this string looks like it lacks
ending
'line identifiers; however, the "typeofcontact" line is part of the first
line. fs
strTblBldqry = "SELECT DISTINCT L8Names.stu_name, L8Names.stu_id,
typeofcontact, activitydate, " & _
"mintimecredit, L8Names.attended INTO ActivityTrker " & _
"FROM L8Names;"

DoCmd.RunSQL strTblBldqry
DoCmd.OpenTable tkrname, acViewNormal
DoCmd.Save acTable, tkrname
DoCmd.Close acTable, tkrname

End Sub

Usually, this only happens if Access can't find the fields in the
table. What is your table structure?

 

[OldPro]

I am trying to take a table [L8Names], which has all of the names that could
be selected as having attended, and it has the following fields: stu_id,
stu_name, attended (which is just a null check box). I am trying to add to
each row of this table then the fields from the unbounded form.

The end results is suppose to be my temporary selection table, which would
then be appended into the master table.

fs

Using Access 2007, I am creating a temporary table [ActivityTrker] which will
feed a master table [MeetingMaster]. The reason for this is because the user
wants to do "bulk" selection and not have to do one person at a time; plus,
the meetings can be one of 30 types, and they can take place any time, any
day of the week; thus, no preloading a table with Meeting date/time and just
asking for attendance.
I have searched this and other forums, asked for help once before, have
researced multiple books/web sites, and not really found any help so far.
Right now, I am not able to get the query to accept the fields coming from
the form as valid fields.
What results from the following code is that the query wants to treat the
input fields as parameters and wants me to re-enter the information, which,
due to the need of having the combo box "comboTypeContact," this is
unacceptable, unless someone knows how to make a combo box a parameter. I am
sure it has been done, but I sure don't know how to do it.
Any help greatly appreciated.
fs
Please note the followings code:
Public Sub BulkMMcmd_Click()
Dim strTblBldqry As String
Dim typeofcontact As String
Dim activitydate As Date
Dim dbs As Database
Dim mintimecredit As Integer
Dim rsTbl As Recordset
Dim attended As CheckBox
Dim tkrname As String
tkrname = "ActivityTrker"
typeofcontact = Me.comboTypeContact.Value
activitydate = Me.dateActivity.Value
mintimecredit = Me.TimeCredit.Value
'Due to the wrapping in the Message box, this string looks like it lacks
ending
'line identifiers; however, the "typeofcontact" line is part of the first
line. fs
strTblBldqry = "SELECT DISTINCT L8Names.stu_name, L8Names.stu_id,
typeofcontact, activitydate, " & _
"mintimecredit, L8Names.attended INTO ActivityTrker " & _
"FROM L8Names;"
DoCmd.RunSQL strTblBldqry
DoCmd.OpenTable tkrname, acViewNormal
DoCmd.Save acTable, tkrname
DoCmd.Close acTable, tkrname
End Sub

Usually, this only happens if Access can't find the fields in the
table. What is your table structure?- Hide quoted text -

- Show quoted text -

I believe you are having some conceptual issues, as this doesn't make
much sense, unless you are leaving out important information. If you
are trying to create an input screen where several records can be
added at one time to a master table, then a new table has to be
created to hold these items until they are appended to the master
table. There are different ways to model the input screen. One way
would be to add one text box for each field, add a command button
which would load the record into the temporary table and a listbox to
display the contents of the temporary table. Another command button
would copy the records to the master table. Another way would be to
create your own grid. It would need scrollbars if all of the records
can not fit on one screen. It would also need editing controls that
are positioned over each cell as it becomes active.

 

[Guest]

Hey, "OldPro":

I feel as if we have made a great breakthrough, even though I am still where
I was.

"If you

are trying to create an input screen where several records can be
added at one time to a master table, then a new table has to be
created to hold these items until they are appended to the master
table."

Exactly. I have a form which has all of the variable information. An
unbounded text box for "Type of Contact," an unbounded text box for "Activity
Date," and finally, an unbounded text box with the "Time credit for the
meeting." I am so sorry that my communication is so bad that I did not get
that across. Of course, it would have helped tremendously that I knew that I
was trying all this time to create an "Input Screen." Who would have thought
it?

After I gather all of the variables, I am then needing to put them into a
table to be able to display them back to the user, along with the "CheckBox"
field that would allow for selection from various ones who are in the cohort.
That is where the problem with the check box comes in. I still am not able
to get that thing to work. I feel that if I could get this corner turned, it
might be enough to at least allow my user to get started and buy me a little
time. Any suggestions?

Again, I am deeply indebted for your kind direction. I do feel like
suddenly, a breath of air has been allowed in.

I am trying to take a table [L8Names], which has all of the names that could
be selected as having attended, and it has the following fields: stu_id,
stu_name, attended (which is just a null check box). I am trying to add to
each row of this table then the fields from the unbounded form.

The end results is suppose to be my temporary selection table, which would
then be appended into the master table.

fs

Using Access 2007, I am creating a temporary table [ActivityTrker] which will
feed a master table [MeetingMaster]. The reason for this is because the user
wants to do "bulk" selection and not have to do one person at a time; plus,
the meetings can be one of 30 types, and they can take place any time, any
day of the week; thus, no preloading a table with Meeting date/time and just
asking for attendance.

I have searched this and other forums, asked for help once before, have
researced multiple books/web sites, and not really found any help so far.
Right now, I am not able to get the query to accept the fields coming from
the form as valid fields.

What results from the following code is that the query wants to treat the
input fields as parameters and wants me to re-enter the information, which,
due to the need of having the combo box "comboTypeContact," this is
unacceptable, unless someone knows how to make a combo box a parameter. I am
sure it has been done, but I sure don't know how to do it.

Any help greatly appreciated.

Please note the followings code:

Public Sub BulkMMcmd_Click()

Dim strTblBldqry As String
Dim typeofcontact As String
Dim activitydate As Date
Dim dbs As Database
Dim mintimecredit As Integer
Dim rsTbl As Recordset
Dim attended As CheckBox
Dim tkrname As String

tkrname = "ActivityTrker"
typeofcontact = Me.comboTypeContact.Value
activitydate = Me.dateActivity.Value
mintimecredit = Me.TimeCredit.Value

'Due to the wrapping in the Message box, this string looks like it lacks
ending
'line identifiers; however, the "typeofcontact" line is part of the first
line. fs
strTblBldqry = "SELECT DISTINCT L8Names.stu_name, L8Names.stu_id,
typeofcontact, activitydate, " & _
"mintimecredit, L8Names.attended INTO ActivityTrker " & _
"FROM L8Names;"

DoCmd.RunSQL strTblBldqry
DoCmd.OpenTable tkrname, acViewNormal
DoCmd.Save acTable, tkrname
DoCmd.Close acTable, tkrname

End Sub

Usually, this only happens if Access can't find the fields in the
table. What is your table structure?- Hide quoted text -

- Show quoted text -

I believe you are having some conceptual issues, as this doesn't make
much sense, unless you are leaving out important information. If you
are trying to create an input screen where several records can be
added at one time to a master table, then a new table has to be
created to hold these items until they are appended to the master
table. There are different ways to model the input screen. One way
would be to add one text box for each field, add a command button
which would load the record into the temporary table and a listbox to
display the contents of the temporary table. Another command button
would copy the records to the master table. Another way would be to
create your own grid. It would need scrollbars if all of the records
can not fit on one screen. It would also need editing controls that
are positioned over each cell as it becomes active.

 

[OldPro]

Hey, "OldPro":

I feel as if we have made a great breakthrough, even though I am still where
I was.

"If you


Exactly. I have a form which has all of the variable information. An
unbounded text box for "Type of Contact," an unbounded text box for "Activity
Date," and finally, an unbounded text box with the "Time credit for the
meeting." I am so sorry that my communication is so bad that I did not get
that across. Of course, it would have helped tremendously that I knew that I
was trying all this time to create an "Input Screen." Who would have thought
it?

After I gather all of the variables, I am then needing to put them into a
table to be able to display them back to the user, along with the "CheckBox"
field that would allow for selection from various ones who are in the cohort.
That is where the problem with the check box comes in. I still am not able
to get that thing to work. I feel that if I could get this corner turned, it
might be enough to at least allow my user to get started and buy me a little
time. Any suggestions?

Again, I am deeply indebted for your kind direction. I do feel like
suddenly, a breath of air has been allowed in.

Enough with the sarcasm, I'll try to be more helpful.
Here is the way that I would do it. You will still need to add the
code for the listbox; if you click a record in the listbox, it should
refresh the edit controls with that record (in case it needs
editing). You may also want a Clear button to clear the input
controls. Setup the combobox cmbName to allow the user to retreive
both name and ID when they select the name. I am using my own names
for everything; I am partial to my own naming conventions. The
functions should be self-explanatory. The combobox and the listbox
are bound controls, the form is not. You probably already know how to
bind them correctly.

Dim sName as string
Dim sID as string
Dim sContactType as string
Dim sActivityDate as string
Dim sMinimumTimeCredit as string
Dim bAttended as boolean

Private sub RefreshScreen
txtName=sName
txtID=sID
txtContactType=sContactType
txtActivityDate=sActivityDate
txtMinimumTimeCredit =sMinimumTimeCredit
chkAttended=bAttended
End sub

Private sub ReadRecord(sRecordKey as string)
Dim db as dao.database
Dim rs as dao.recordset
set db=currentdb()
set rs=db.openrecordset("tblActivityTracker",dbOpenSnapshot)
With rs
.FindFirst "[ID]=" & sRecordKey
If Not .NoMatch then
sName = .Name
sID = .ID
sContactType = .ContactType
sActivityDate = .ActivityDate
sMinimumTimeCredit = .MinimumTimeCredit
bAttended = .Attended
Endif
End with
rs.close
set rs=nothing
db.close
set db=nothing
End sub

Private sub WriteRecord(sRecordKey as string)
Dim db as dao.database
Dim rs as dao.recordset
set db=currentdb()
set rs=db.openrecordset("tblActivityTracker",dbOpenDynaset)
With rs
.FindFirst "[ID]='" & sRecordKey & "'"
If Not .NoMatch then
.Edit
Else
.Addnew
Endif
.Name = sName
.ID = sID
.ContactType = sContactType
.ActivityDate = cdate(sActivityDate)
.MinimunTimeCredit = sMinimumTimeCredit
.Attended = bAttended
.Update
End With
End sub

Private sub SaveEdit
sName = cmbName
sID = txtID
sContactType=txtContactType
sActivityDate = txtActivityDate
sMinimumTimeCredit = txtMinimumTimeCredit
bAttended = chkAttended
End sub

Private sub cmdAdd_click
SaveEdit
WriteRecord txtID
ClearEdit
End sub

Private sub ClearEdit
cmbName = ""
txtID = ""
txtContactType = ""
txtActivityDate= ""
txtMinimumTimeCredit = ""
chkAttended = false
sName = ""
sID = ""
sContactType = ""
sActivityDate = ""
sMinimumTimeCredit = ""
bAttended = false
End sub

Private sub cmdBatchAdd
Dim db as dao.database

set db=currentdb()
db.execute "Insert Into tblMeetingMaster
(Name,ID,ContactType,ActivityDate,MinimumTimeCredit,Attended) Select
Name, ID,ContactType,ActivityDate, MinimumTimeCredit,Attended From
tblActivityTracker"
db.close
set db = nothing
' Put code here to refresh the listbox

ClearEdit
End sub

 

[Guest]

Believe me when I say I am truly thankful for any and all help. I have been
programming since the early 80's starting with a T.I. DS990 (I believe it
was) Model 1, have even done the I.B.M. stint and zoomed along just fine
until trying to tangle with VB and its step-child VBA. Never in all of my
life have I seen so much effort to do so little.

Anyway, I do appreciate your help. No, it probably is not a safe bet to say
I know the proper way to do anything. Not from lack of trying. Just a lack
of connecting the dots. But this does help. My poor end user who has to
start using this Monday will get something, but I am not sure if they will
ever get a "bulk selector." Not your fault, by any means. Mine totally.
Well, thanks again.

Your totally indebted servant,
Frank

Hey, "OldPro":

I feel as if we have made a great breakthrough, even though I am still where
I was.

"If you


Exactly. I have a form which has all of the variable information. An
unbounded text box for "Type of Contact," an unbounded text box for "Activity
Date," and finally, an unbounded text box with the "Time credit for the
meeting." I am so sorry that my communication is so bad that I did not get
that across. Of course, it would have helped tremendously that I knew that I
was trying all this time to create an "Input Screen." Who would have thought
it?

After I gather all of the variables, I am then needing to put them into a
table to be able to display them back to the user, along with the "CheckBox"
field that would allow for selection from various ones who are in the cohort.
That is where the problem with the check box comes in. I still am not able
to get that thing to work. I feel that if I could get this corner turned, it
might be enough to at least allow my user to get started and buy me a little
time. Any suggestions?

Again, I am deeply indebted for your kind direction. I do feel like
suddenly, a breath of air has been allowed in.

Enough with the sarcasm, I'll try to be more helpful.
Here is the way that I would do it. You will still need to add the
code for the listbox; if you click a record in the listbox, it should
refresh the edit controls with that record (in case it needs
editing). You may also want a Clear button to clear the input
controls. Setup the combobox cmbName to allow the user to retreive
both name and ID when they select the name. I am using my own names
for everything; I am partial to my own naming conventions. The
functions should be self-explanatory. The combobox and the listbox
are bound controls, the form is not. You probably already know how to
bind them correctly.

Dim sName as string
Dim sID as string
Dim sContactType as string
Dim sActivityDate as string
Dim sMinimumTimeCredit as string
Dim bAttended as boolean

Private sub RefreshScreen
txtName=sName
txtID=sID
txtContactType=sContactType
txtActivityDate=sActivityDate
txtMinimumTimeCredit =sMinimumTimeCredit
chkAttended=bAttended
End sub

Private sub ReadRecord(sRecordKey as string)
Dim db as dao.database
Dim rs as dao.recordset
set db=currentdb()
set rs=db.openrecordset("tblActivityTracker",dbOpenSnapshot)
With rs
.FindFirst "[ID]=" & sRecordKey
If Not .NoMatch then
sName = .Name
sID = .ID
sContactType = .ContactType
sActivityDate = .ActivityDate
sMinimumTimeCredit = .MinimumTimeCredit
bAttended = .Attended
Endif
End with
rs.close
set rs=nothing
db.close
set db=nothing
End sub

Private sub WriteRecord(sRecordKey as string)
Dim db as dao.database
Dim rs as dao.recordset
set db=currentdb()
set rs=db.openrecordset("tblActivityTracker",dbOpenDynaset)
With rs
.FindFirst "[ID]='" & sRecordKey & "'"
If Not .NoMatch then
.Edit
Else
.Addnew
Endif
.Name = sName
.ID = sID
.ContactType = sContactType
.ActivityDate = cdate(sActivityDate)
.MinimunTimeCredit = sMinimumTimeCredit
.Attended = bAttended
.Update
End With
End sub

Private sub SaveEdit
sName = cmbName
sID = txtID
sContactType=txtContactType
sActivityDate = txtActivityDate
sMinimumTimeCredit = txtMinimumTimeCredit
bAttended = chkAttended
End sub

Private sub cmdAdd_click
SaveEdit
WriteRecord txtID
ClearEdit
End sub

Private sub ClearEdit
cmbName = ""
txtID = ""
txtContactType = ""
txtActivityDate= ""
txtMinimumTimeCredit = ""
chkAttended = false
sName = ""
sID = ""
sContactType = ""
sActivityDate = ""
sMinimumTimeCredit = ""
bAttended = false
End sub

Private sub cmdBatchAdd
Dim db as dao.database

set db=currentdb()
db.execute "Insert Into tblMeetingMaster
(Name,ID,ContactType,ActivityDate,MinimumTimeCredit,Attended) Select
Name, ID,ContactType,ActivityDate, MinimumTimeCredit,Attended From
tblActivityTracker"
db.close
set db = nothing
' Put code here to refresh the listbox

ClearEdit
End sub