Too few parameters. Expected 1.

[Damon]

Hi there. I have found a few posts with this problem but their solutions did
not work for me.

My DB is for recording the personnel of a ficticious military. I am building
forms for entering all the information. My main form consists of a Tab group
which separates the ifnormation by category. One of the tabs displays the
commendations awarded to a specific soldier/record. the commendations are
displayed as a contiuous subform. Whereas almost all my details for a soldier
is held in one table, the commendation list, with descriptions for what the
commendations are for, and the awarded commendations are in separate tables.

The Commendations table has 5 fields: CommendationName(txt/PK),
CommendationDescription(memo), CommendationMedal(oleObject),
CommendationRibbon(oleObject), and Ribbon(Yes/No). The COmmendationMedal and
CommendationRibbon fileds hold images of what the medal looks like and if it
has an accompanying ribbon, what it looks like too. The ribbon field is used
to determine if the commendation has a ribbon version.

The Awarded Commendations table has 3 fields: RebelID(txt/FK),
Commendation(txt/FK), and CommendationReason(memo). All together is the PK.
This table holds all the soldiers commendations and the reasons for awarding
them.

OK. Now, on the tab with the subform that shows all of a soldier's
commendations there is a command button that is used to add a commendation.
When pressed it opens a new form that has 6 information fields. They are all
unbound. The first is a listbox bound to a query that populates it with all
the available commendations. The next is a textbox that displays the
description of the commendation when it is clicked on in the list box. there
are 2 bound object frames for the images of the selected commendation, a
textbox that holds the RebelID of the current open soldier and a final
textbox that is for entering the reason for awarding the commendation. I
populate all the controls except for the RebelID and Reason for awarding
textboxes with the update event of the listbox. I pass the RebelID to its
textbox when the form is opened.

The user can do the following things in the form: Click on a commendation in
the list box, enter text in the reason for awarding textbox, click the
confirm commendation cmd button, and click the close form cmd button. The
problem I am having is with the code to add the commendation to the awarded
commendations table that is attached to the click event of the confirm
commendation cmd button.

The code I got from Ofer in another thread (thanks!). When I click on the
confirm commendation button I get the error message in the subject line. I
have ensured that the Microsoft DAO 3.6 Object Library is checked in the
references. Here is the code:

Private Sub cmd_confirmcommendation_Click()
On Error GoTo Err_cmd_confirmcommendation_Click

Dim MyDb As Database, Myrec As Recordset
Set MyDb = CodeDb ' Current MDB
Set Myrec = MyDb.OpenRecordset("Select * From AwardedCommendations where
RedelID = " & Me.[txt_id])

Myrec.AddNew
Myrec!RebelID = Me.txt_id
Myrec!Commendation = Me.[lst_commendation]
Myrec!CommendationNotes = Me.[txt_reason]
Myrec.Update

Me.txt_reason = ""
Me.lst_commendation.SetFocus

Exit_cmd_confirmcommendation_Click:
Exit Sub

Err_cmd_confirmcommendation_Click:
MsgBox Err.Description
Resume Exit_cmd_confirmcommendation_Click

End Sub

Thanks in advance for the help.

Damon

 

[Douglas J. Steele]

Is that a literal cut-and-paste? If so, you've got RedelID = in the query,
not RebelID =

If your query actually has the field name spelled correctly, what's the
database type of RebelID? If it's text, you need to put quotes around the
value you're using:

Set Myrec = MyDb.OpenRecordset( _
"Select * From AwardedCommendations where " & _
" RebelID = " & Chr$(34) & Me.[txt_id] & Chr$(34))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi there. I have found a few posts with this problem but their solutions
did
not work for me.

My DB is for recording the personnel of a ficticious military. I am
building
forms for entering all the information. My main form consists of a Tab
group
which separates the ifnormation by category. One of the tabs displays the
commendations awarded to a specific soldier/record. the commendations are
displayed as a contiuous subform. Whereas almost all my details for a
soldier
is held in one table, the commendation list, with descriptions for what
the
commendations are for, and the awarded commendations are in separate
tables.

The Commendations table has 5 fields: CommendationName(txt/PK),
CommendationDescription(memo), CommendationMedal(oleObject),
CommendationRibbon(oleObject), and Ribbon(Yes/No). The COmmendationMedal
and
CommendationRibbon fileds hold images of what the medal looks like and if
it
has an accompanying ribbon, what it looks like too. The ribbon field is
used
to determine if the commendation has a ribbon version.

The Awarded Commendations table has 3 fields: RebelID(txt/FK),
Commendation(txt/FK), and CommendationReason(memo). All together is the
PK.
This table holds all the soldiers commendations and the reasons for
awarding
them.

OK. Now, on the tab with the subform that shows all of a soldier's
commendations there is a command button that is used to add a
commendation.
When pressed it opens a new form that has 6 information fields. They are
all
unbound. The first is a listbox bound to a query that populates it with
all
the available commendations. The next is a textbox that displays the
description of the commendation when it is clicked on in the list box.
there
are 2 bound object frames for the images of the selected commendation, a
textbox that holds the RebelID of the current open soldier and a final
textbox that is for entering the reason for awarding the commendation. I
populate all the controls except for the RebelID and Reason for awarding
textboxes with the update event of the listbox. I pass the RebelID to its
textbox when the form is opened.

The user can do the following things in the form: Click on a commendation
in
the list box, enter text in the reason for awarding textbox, click the
confirm commendation cmd button, and click the close form cmd button. The
problem I am having is with the code to add the commendation to the
awarded
commendations table that is attached to the click event of the confirm
commendation cmd button.

The code I got from Ofer in another thread (thanks!). When I click on the
confirm commendation button I get the error message in the subject line. I
have ensured that the Microsoft DAO 3.6 Object Library is checked in the
references. Here is the code:

Private Sub cmd_confirmcommendation_Click()
On Error GoTo Err_cmd_confirmcommendation_Click

Dim MyDb As Database, Myrec As Recordset
Set MyDb = CodeDb ' Current MDB
Set Myrec = MyDb.OpenRecordset("Select * From AwardedCommendations
where
RedelID = " & Me.[txt_id])

Myrec.AddNew
Myrec!RebelID = Me.txt_id
Myrec!Commendation = Me.[lst_commendation]
Myrec!CommendationNotes = Me.[txt_reason]
Myrec.Update

Me.txt_reason = ""
Me.lst_commendation.SetFocus

Exit_cmd_confirmcommendation_Click:
Exit Sub

Err_cmd_confirmcommendation_Click:
MsgBox Err.Description
Resume Exit_cmd_confirmcommendation_Click

End Sub

Thanks in advance for the help.

Damon

 

[Ofer]

Hi Damon
If all you want to do is add a new record, then there is no reason to open
the recordset with a filter, just use that

Set Myrec = MyDb.OpenRecordset("Select * From AwardedCommendations")

Myrec.AddNew
Myrec!RebelID = Me.txt_id
Myrec!Commendation = Me.[lst_commendation]
Myrec!CommendationNotes = Me.[txt_reason]
Myrec.Update
========================================
The reason you use the filter on the open recordset will be, to check if the
record already exist, if it does, we either edit it, or we wont do any thing

Set Myrec = MyDb.OpenRecordset("Select * From AwardedCommendations where
RedelID = " & Me.[txt_id])
If Myrec.Eof then
Myrec.AddNew
Myrec!RebelID = Me.txt_id
Myrec!Commendation = Me.[lst_commendation]
Myrec!CommendationNotes = Me.[txt_reason]
Myrec.Update
End If
=========================================
Now, about the error you are getting, check if the Me.[txt_id] return any
value, if it doesnt then the actual SQL look like that
Select * From AwardedCommendations where RedelID =

So the sql looks for something after the equal sign, and then it gives that
message
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benefit from it.

Good luck

Hi there. I have found a few posts with this problem but their solutions did
not work for me.

My DB is for recording the personnel of a ficticious military. I am building
forms for entering all the information. My main form consists of a Tab group
which separates the ifnormation by category. One of the tabs displays the
commendations awarded to a specific soldier/record. the commendations are
displayed as a contiuous subform. Whereas almost all my details for a soldier
is held in one table, the commendation list, with descriptions for what the
commendations are for, and the awarded commendations are in separate tables.

The Commendations table has 5 fields: CommendationName(txt/PK),
CommendationDescription(memo), CommendationMedal(oleObject),
CommendationRibbon(oleObject), and Ribbon(Yes/No). The COmmendationMedal and
CommendationRibbon fileds hold images of what the medal looks like and if it
has an accompanying ribbon, what it looks like too. The ribbon field is used
to determine if the commendation has a ribbon version.

The Awarded Commendations table has 3 fields: RebelID(txt/FK),
Commendation(txt/FK), and CommendationReason(memo). All together is the PK.
This table holds all the soldiers commendations and the reasons for awarding
them.

OK. Now, on the tab with the subform that shows all of a soldier's
commendations there is a command button that is used to add a commendation.
When pressed it opens a new form that has 6 information fields. They are all
unbound. The first is a listbox bound to a query that populates it with all
the available commendations. The next is a textbox that displays the
description of the commendation when it is clicked on in the list box. there
are 2 bound object frames for the images of the selected commendation, a
textbox that holds the RebelID of the current open soldier and a final
textbox that is for entering the reason for awarding the commendation. I
populate all the controls except for the RebelID and Reason for awarding
textboxes with the update event of the listbox. I pass the RebelID to its
textbox when the form is opened.

The user can do the following things in the form: Click on a commendation in
the list box, enter text in the reason for awarding textbox, click the
confirm commendation cmd button, and click the close form cmd button. The
problem I am having is with the code to add the commendation to the awarded
commendations table that is attached to the click event of the confirm
commendation cmd button.

The code I got from Ofer in another thread (thanks!). When I click on the
confirm commendation button I get the error message in the subject line. I
have ensured that the Microsoft DAO 3.6 Object Library is checked in the
references. Here is the code:

Private Sub cmd_confirmcommendation_Click()
On Error GoTo Err_cmd_confirmcommendation_Click

Dim MyDb As Database, Myrec As Recordset
Set MyDb = CodeDb ' Current MDB
Set Myrec = MyDb.OpenRecordset("Select * From AwardedCommendations where
RedelID = " & Me.[txt_id])

Myrec.AddNew
Myrec!RebelID = Me.txt_id
Myrec!Commendation = Me.[lst_commendation]
Myrec!CommendationNotes = Me.[txt_reason]
Myrec.Update

Me.txt_reason = ""
Me.lst_commendation.SetFocus

Exit_cmd_confirmcommendation_Click:
Exit Sub

Err_cmd_confirmcommendation_Click:
MsgBox Err.Description
Resume Exit_cmd_confirmcommendation_Click

End Sub

Thanks in advance for the help.

Damon

 

['69 Camaro]

Hi, Damon.

You've misspelled RebelID as RedelID, and the Awarded Commendations table in
your description is two words, while the code shows one continuous word for
the table name in the SQL statement, so ensure that the code uses the correct
one. Unless this is a library database or Add-in, you should be using
CurrentDB( ), not CodeDb. If you have both the DAO library and ADO library,
then you should precede the Recordset class name with DAO to disambiguate the
class. For example:

Dim Myrec As DAO.Recordset

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

Hi there. I have found a few posts with this problem but their solutions did
not work for me.

My DB is for recording the personnel of a ficticious military. I am building
forms for entering all the information. My main form consists of a Tab group
which separates the ifnormation by category. One of the tabs displays the
commendations awarded to a specific soldier/record. the commendations are
displayed as a contiuous subform. Whereas almost all my details for a soldier
is held in one table, the commendation list, with descriptions for what the
commendations are for, and the awarded commendations are in separate tables.

The Commendations table has 5 fields: CommendationName(txt/PK),
CommendationDescription(memo), CommendationMedal(oleObject),
CommendationRibbon(oleObject), and Ribbon(Yes/No). The COmmendationMedal and
CommendationRibbon fileds hold images of what the medal looks like and if it
has an accompanying ribbon, what it looks like too. The ribbon field is used
to determine if the commendation has a ribbon version.

The Awarded Commendations table has 3 fields: RebelID(txt/FK),
Commendation(txt/FK), and CommendationReason(memo). All together is the PK.
This table holds all the soldiers commendations and the reasons for awarding
them.

OK. Now, on the tab with the subform that shows all of a soldier's
commendations there is a command button that is used to add a commendation.
When pressed it opens a new form that has 6 information fields. They are all
unbound. The first is a listbox bound to a query that populates it with all
the available commendations. The next is a textbox that displays the
description of the commendation when it is clicked on in the list box. there
are 2 bound object frames for the images of the selected commendation, a
textbox that holds the RebelID of the current open soldier and a final
textbox that is for entering the reason for awarding the commendation. I
populate all the controls except for the RebelID and Reason for awarding
textboxes with the update event of the listbox. I pass the RebelID to its
textbox when the form is opened.

The user can do the following things in the form: Click on a commendation in
the list box, enter text in the reason for awarding textbox, click the
confirm commendation cmd button, and click the close form cmd button. The
problem I am having is with the code to add the commendation to the awarded
commendations table that is attached to the click event of the confirm
commendation cmd button.

The code I got from Ofer in another thread (thanks!). When I click on the
confirm commendation button I get the error message in the subject line. I
have ensured that the Microsoft DAO 3.6 Object Library is checked in the
references. Here is the code:

Private Sub cmd_confirmcommendation_Click()
On Error GoTo Err_cmd_confirmcommendation_Click

Dim MyDb As Database, Myrec As Recordset
Set MyDb = CodeDb ' Current MDB
Set Myrec = MyDb.OpenRecordset("Select * From AwardedCommendations where
RedelID = " & Me.[txt_id])

Myrec.AddNew
Myrec!RebelID = Me.txt_id
Myrec!Commendation = Me.[lst_commendation]
Myrec!CommendationNotes = Me.[txt_reason]
Myrec.Update

Me.txt_reason = ""
Me.lst_commendation.SetFocus

Exit_cmd_confirmcommendation_Click:
Exit Sub

Err_cmd_confirmcommendation_Click:
MsgBox Err.Description
Resume Exit_cmd_confirmcommendation_Click

End Sub

Thanks in advance for the help.

Damon

 

[Damon]

The database type of RebelID is number. How do I put the value of a text box
into a number datatype? I am now getting a Type Mismatch error.

By the way, yours and Ofer's and Camaro69's help is awesome. Thank you.

Is that a literal cut-and-paste? If so, you've got RedelID = in the query,
not RebelID =

If your query actually has the field name spelled correctly, what's the
database type of RebelID? If it's text, you need to put quotes around the
value you're using:

Set Myrec = MyDb.OpenRecordset( _
"Select * From AwardedCommendations where " & _
" RebelID = " & Chr$(34) & Me.[txt_id] & Chr$(34))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi there. I have found a few posts with this problem but their solutions
did
not work for me.

My DB is for recording the personnel of a ficticious military. I am
building
forms for entering all the information. My main form consists of a Tab
group
which separates the ifnormation by category. One of the tabs displays the
commendations awarded to a specific soldier/record. the commendations are
displayed as a contiuous subform. Whereas almost all my details for a
soldier
is held in one table, the commendation list, with descriptions for what
the
commendations are for, and the awarded commendations are in separate
tables.

The Commendations table has 5 fields: CommendationName(txt/PK),
CommendationDescription(memo), CommendationMedal(oleObject),
CommendationRibbon(oleObject), and Ribbon(Yes/No). The COmmendationMedal
and
CommendationRibbon fileds hold images of what the medal looks like and if
it
has an accompanying ribbon, what it looks like too. The ribbon field is
used
to determine if the commendation has a ribbon version.

The Awarded Commendations table has 3 fields: RebelID(txt/FK),
Commendation(txt/FK), and CommendationReason(memo). All together is the
PK.
This table holds all the soldiers commendations and the reasons for
awarding
them.

OK. Now, on the tab with the subform that shows all of a soldier's
commendations there is a command button that is used to add a
commendation.
When pressed it opens a new form that has 6 information fields. They are
all
unbound. The first is a listbox bound to a query that populates it with
all
the available commendations. The next is a textbox that displays the
description of the commendation when it is clicked on in the list box.
there
are 2 bound object frames for the images of the selected commendation, a
textbox that holds the RebelID of the current open soldier and a final
textbox that is for entering the reason for awarding the commendation. I
populate all the controls except for the RebelID and Reason for awarding
textboxes with the update event of the listbox. I pass the RebelID to its
textbox when the form is opened.

The user can do the following things in the form: Click on a commendation
in
the list box, enter text in the reason for awarding textbox, click the
confirm commendation cmd button, and click the close form cmd button. The
problem I am having is with the code to add the commendation to the
awarded
commendations table that is attached to the click event of the confirm
commendation cmd button.

The code I got from Ofer in another thread (thanks!). When I click on the
confirm commendation button I get the error message in the subject line. I
have ensured that the Microsoft DAO 3.6 Object Library is checked in the
references. Here is the code:

Private Sub cmd_confirmcommendation_Click()
On Error GoTo Err_cmd_confirmcommendation_Click

Dim MyDb As Database, Myrec As Recordset
Set MyDb = CodeDb ' Current MDB
Set Myrec = MyDb.OpenRecordset("Select * From AwardedCommendations
where
RedelID = " & Me.[txt_id])

Myrec.AddNew
Myrec!RebelID = Me.txt_id
Myrec!Commendation = Me.[lst_commendation]
Myrec!CommendationNotes = Me.[txt_reason]
Myrec.Update

Me.txt_reason = ""
Me.lst_commendation.SetFocus

Exit_cmd_confirmcommendation_Click:
Exit Sub

Err_cmd_confirmcommendation_Click:
MsgBox Err.Description
Resume Exit_cmd_confirmcommendation_Click

End Sub

Thanks in advance for the help.

Damon

 

[Douglas J Steele]

If you copied what I suggested, and it's numeric, not text, remove the two
invocations of Chr$(34):

Set Myrec = MyDb.OpenRecordset( _
"Select * From AwardedCommendations where " & _
" RebelID = " & Me.[txt_id])

I did indicate that you only needed that code if RebelID was text.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The database type of RebelID is number. How do I put the value of a text box
into a number datatype? I am now getting a Type Mismatch error.

By the way, yours and Ofer's and Camaro69's help is awesome. Thank you.

Is that a literal cut-and-paste? If so, you've got RedelID = in the query,
not RebelID =

If your query actually has the field name spelled correctly, what's the
database type of RebelID? If it's text, you need to put quotes around the
value you're using:

Set Myrec = MyDb.OpenRecordset( _
"Select * From AwardedCommendations where " & _
" RebelID = " & Chr$(34) & Me.[txt_id] & Chr$(34))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi there. I have found a few posts with this problem but their solutions
did
not work for me.

My DB is for recording the personnel of a ficticious military. I am
building
forms for entering all the information. My main form consists of a Tab
group
which separates the ifnormation by category. One of the tabs displays the
commendations awarded to a specific soldier/record. the commendations are
displayed as a contiuous subform. Whereas almost all my details for a
soldier
is held in one table, the commendation list, with descriptions for what
the
commendations are for, and the awarded commendations are in separate
tables.

The Commendations table has 5 fields: CommendationName(txt/PK),
CommendationDescription(memo), CommendationMedal(oleObject),
CommendationRibbon(oleObject), and Ribbon(Yes/No). The COmmendationMedal
and
CommendationRibbon fileds hold images of what the medal looks like and if
it
has an accompanying ribbon, what it looks like too. The ribbon field is
used
to determine if the commendation has a ribbon version.

The Awarded Commendations table has 3 fields: RebelID(txt/FK),
Commendation(txt/FK), and CommendationReason(memo). All together is the
PK.
This table holds all the soldiers commendations and the reasons for
awarding
them.

OK. Now, on the tab with the subform that shows all of a soldier's
commendations there is a command button that is used to add a
commendation.
When pressed it opens a new form that has 6 information fields. They are
all
unbound. The first is a listbox bound to a query that populates it with
all
the available commendations. The next is a textbox that displays the
description of the commendation when it is clicked on in the list box.
there
are 2 bound object frames for the images of the selected commendation, a
textbox that holds the RebelID of the current open soldier and a final
textbox that is for entering the reason for awarding the commendation. I
populate all the controls except for the RebelID and Reason for awarding
textboxes with the update event of the listbox. I pass the RebelID to its
textbox when the form is opened.

The user can do the following things in the form: Click on a commendation
in
the list box, enter text in the reason for awarding textbox, click the
confirm commendation cmd button, and click the close form cmd button. The
problem I am having is with the code to add the commendation to the
awarded
commendations table that is attached to the click event of the confirm
commendation cmd button.

The code I got from Ofer in another thread (thanks!). When I click on the
confirm commendation button I get the error message in the subject line. I
have ensured that the Microsoft DAO 3.6 Object Library is checked in the
references. Here is the code:

Private Sub cmd_confirmcommendation_Click()
On Error GoTo Err_cmd_confirmcommendation_Click

Dim MyDb As Database, Myrec As Recordset
Set MyDb = CodeDb ' Current MDB
Set Myrec = MyDb.OpenRecordset("Select * From AwardedCommendations
where
RedelID = " & Me.[txt_id])

Myrec.AddNew
Myrec!RebelID = Me.txt_id
Myrec!Commendation = Me.[lst_commendation]
Myrec!CommendationNotes = Me.[txt_reason]
Myrec.Update

Me.txt_reason = ""
Me.lst_commendation.SetFocus

Exit_cmd_confirmcommendation_Click:
Exit Sub

Err_cmd_confirmcommendation_Click:
MsgBox Err.Description
Resume Exit_cmd_confirmcommendation_Click

End Sub

Thanks in advance for the help.

Damon

 

[Damon]

Actually, I realized that the field doesn't need to be number (I had it set
to autonumber) So I changed it to text, but I am still getting a type
mismatch error.

The database type of RebelID is number. How do I put the value of a text box
into a number datatype? I am now getting a Type Mismatch error.

By the way, yours and Ofer's and Camaro69's help is awesome. Thank you.

Is that a literal cut-and-paste? If so, you've got RedelID = in the query,
not RebelID =

If your query actually has the field name spelled correctly, what's the
database type of RebelID? If it's text, you need to put quotes around the
value you're using:

Set Myrec = MyDb.OpenRecordset( _
"Select * From AwardedCommendations where " & _
" RebelID = " & Chr$(34) & Me.[txt_id] & Chr$(34))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi there. I have found a few posts with this problem but their solutions
did
not work for me.

My DB is for recording the personnel of a ficticious military. I am
building
forms for entering all the information. My main form consists of a Tab
group
which separates the ifnormation by category. One of the tabs displays the
commendations awarded to a specific soldier/record. the commendations are
displayed as a contiuous subform. Whereas almost all my details for a
soldier
is held in one table, the commendation list, with descriptions for what
the
commendations are for, and the awarded commendations are in separate
tables.

The Commendations table has 5 fields: CommendationName(txt/PK),
CommendationDescription(memo), CommendationMedal(oleObject),
CommendationRibbon(oleObject), and Ribbon(Yes/No). The COmmendationMedal
and
CommendationRibbon fileds hold images of what the medal looks like and if
it
has an accompanying ribbon, what it looks like too. The ribbon field is
used
to determine if the commendation has a ribbon version.

The Awarded Commendations table has 3 fields: RebelID(txt/FK),
Commendation(txt/FK), and CommendationReason(memo). All together is the
PK.
This table holds all the soldiers commendations and the reasons for
awarding
them.

OK. Now, on the tab with the subform that shows all of a soldier's
commendations there is a command button that is used to add a
commendation.
When pressed it opens a new form that has 6 information fields. They are
all
unbound. The first is a listbox bound to a query that populates it with
all
the available commendations. The next is a textbox that displays the
description of the commendation when it is clicked on in the list box.
there
are 2 bound object frames for the images of the selected commendation, a
textbox that holds the RebelID of the current open soldier and a final
textbox that is for entering the reason for awarding the commendation. I
populate all the controls except for the RebelID and Reason for awarding
textboxes with the update event of the listbox. I pass the RebelID to its
textbox when the form is opened.

The user can do the following things in the form: Click on a commendation
in
the list box, enter text in the reason for awarding textbox, click the
confirm commendation cmd button, and click the close form cmd button. The
problem I am having is with the code to add the commendation to the
awarded
commendations table that is attached to the click event of the confirm
commendation cmd button.

The code I got from Ofer in another thread (thanks!). When I click on the
confirm commendation button I get the error message in the subject line. I
have ensured that the Microsoft DAO 3.6 Object Library is checked in the
references. Here is the code:

Private Sub cmd_confirmcommendation_Click()
On Error GoTo Err_cmd_confirmcommendation_Click

Dim MyDb As Database, Myrec As Recordset
Set MyDb = CodeDb ' Current MDB
Set Myrec = MyDb.OpenRecordset("Select * From AwardedCommendations
where
RedelID = " & Me.[txt_id])

Myrec.AddNew
Myrec!RebelID = Me.txt_id
Myrec!Commendation = Me.[lst_commendation]
Myrec!CommendationNotes = Me.[txt_reason]
Myrec.Update

Me.txt_reason = ""
Me.lst_commendation.SetFocus

Exit_cmd_confirmcommendation_Click:
Exit Sub

Err_cmd_confirmcommendation_Click:
MsgBox Err.Description
Resume Exit_cmd_confirmcommendation_Click

End Sub

Thanks in advance for the help.

Damon

 

[Damon]

I also took Ofer's suggestion and got rid of the criteria part of the sql
statement, so now I just have: Set Myrec = MyDb.OpenRecordset("Select * From
AwardedCommendations")

Actually, I realized that the field doesn't need to be number (I had it set
to autonumber) So I changed it to text, but I am still getting a type
mismatch error.

The database type of RebelID is number. How do I put the value of a text box
into a number datatype? I am now getting a Type Mismatch error.

By the way, yours and Ofer's and Camaro69's help is awesome. Thank you.

Is that a literal cut-and-paste? If so, you've got RedelID = in the query,
not RebelID =

If your query actually has the field name spelled correctly, what's the
database type of RebelID? If it's text, you need to put quotes around the
value you're using:

Set Myrec = MyDb.OpenRecordset( _
"Select * From AwardedCommendations where " & _
" RebelID = " & Chr$(34) & Me.[txt_id] & Chr$(34))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Hi there. I have found a few posts with this problem but their solutions
did
not work for me.

My DB is for recording the personnel of a ficticious military. I am
building
forms for entering all the information. My main form consists of a Tab
group
which separates the ifnormation by category. One of the tabs displays the
commendations awarded to a specific soldier/record. the commendations are
displayed as a contiuous subform. Whereas almost all my details for a
soldier
is held in one table, the commendation list, with descriptions for what
the
commendations are for, and the awarded commendations are in separate
tables.

The Commendations table has 5 fields: CommendationName(txt/PK),
CommendationDescription(memo), CommendationMedal(oleObject),
CommendationRibbon(oleObject), and Ribbon(Yes/No). The COmmendationMedal
and
CommendationRibbon fileds hold images of what the medal looks like and if
it
has an accompanying ribbon, what it looks like too. The ribbon field is
used
to determine if the commendation has a ribbon version.

The Awarded Commendations table has 3 fields: RebelID(txt/FK),
Commendation(txt/FK), and CommendationReason(memo). All together is the
PK.
This table holds all the soldiers commendations and the reasons for
awarding
them.

OK. Now, on the tab with the subform that shows all of a soldier's
commendations there is a command button that is used to add a
commendation.
When pressed it opens a new form that has 6 information fields. They are
all
unbound. The first is a listbox bound to a query that populates it with
all
the available commendations. The next is a textbox that displays the
description of the commendation when it is clicked on in the list box.
there
are 2 bound object frames for the images of the selected commendation, a
textbox that holds the RebelID of the current open soldier and a final
textbox that is for entering the reason for awarding the commendation. I
populate all the controls except for the RebelID and Reason for awarding
textboxes with the update event of the listbox. I pass the RebelID to its
textbox when the form is opened.

The user can do the following things in the form: Click on a commendation
in
the list box, enter text in the reason for awarding textbox, click the
confirm commendation cmd button, and click the close form cmd button. The
problem I am having is with the code to add the commendation to the
awarded
commendations table that is attached to the click event of the confirm
commendation cmd button.

The code I got from Ofer in another thread (thanks!). When I click on the
confirm commendation button I get the error message in the subject line. I
have ensured that the Microsoft DAO 3.6 Object Library is checked in the
references. Here is the code:

Private Sub cmd_confirmcommendation_Click()
On Error GoTo Err_cmd_confirmcommendation_Click

Dim MyDb As Database, Myrec As Recordset
Set MyDb = CodeDb ' Current MDB
Set Myrec = MyDb.OpenRecordset("Select * From AwardedCommendations
where
RedelID = " & Me.[txt_id])

Myrec.AddNew
Myrec!RebelID = Me.txt_id
Myrec!Commendation = Me.[lst_commendation]
Myrec!CommendationNotes = Me.[txt_reason]
Myrec.Update

Me.txt_reason = ""
Me.lst_commendation.SetFocus

Exit_cmd_confirmcommendation_Click:
Exit Sub

Err_cmd_confirmcommendation_Click:
MsgBox Err.Description
Resume Exit_cmd_confirmcommendation_Click

End Sub

Thanks in advance for the help.

Damon

 

[Damon]

OK, let me clarify things. Here is my current incarnation of the code:

Private Sub cmd_confirmcommendation_Click()
On Error GoTo Err_cmd_confirmcommendation_Click

Dim MyDb As Database, Myrec As Recordset
Set MyDb = CurrentDb() ' Current MDB
Set Myrec = MyDb.OpenRecordset("Select * From AwardedCommendations")

Myrec.AddNew
Myrec!RebelID = Me.txt_id
Myrec!Commendation = Me.[lst_commendation]
Myrec!CommendationNotes = Me.[txt_reason]
Myrec.Update

Me.txt_reason = ""
Me.lst_commendation.SetFocus

Exit_cmd_confirmcommendation_Click:
Exit Sub

Err_cmd_confirmcommendation_Click:
MsgBox Err.Description
Resume Exit_cmd_confirmcommendation_Click

End Sub

Now, I have made changed the datatype for RebelID back to number but I
didn't make it autonumber. I placed breakpoints throughout the code to find
out when the Type Mismatch error was occuring. It would occur when the Set
MyRec line executed. Hope that helps you help me

Damon

I also took Ofer's suggestion and got rid of the criteria part of the sql
statement, so now I just have: Set Myrec = MyDb.OpenRecordset("Select * From
AwardedCommendations")

Actually, I realized that the field doesn't need to be number (I had it set
to autonumber) So I changed it to text, but I am still getting a type
mismatch error.

The database type of RebelID is number. How do I put the value of a text box
into a number datatype? I am now getting a Type Mismatch error.

By the way, yours and Ofer's and Camaro69's help is awesome. Thank you.

:

Is that a literal cut-and-paste? If so, you've got RedelID = in the query,
not RebelID =

If your query actually has the field name spelled correctly, what's the
database type of RebelID? If it's text, you need to put quotes around the
value you're using:

Set Myrec = MyDb.OpenRecordset( _
"Select * From AwardedCommendations where " & _
" RebelID = " & Chr$(34) & Me.[txt_id] & Chr$(34))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Hi there. I have found a few posts with this problem but their solutions
did
not work for me.

My DB is for recording the personnel of a ficticious military. I am
building
forms for entering all the information. My main form consists of a Tab
group
which separates the ifnormation by category. One of the tabs displays the
commendations awarded to a specific soldier/record. the commendations are
displayed as a contiuous subform. Whereas almost all my details for a
soldier
is held in one table, the commendation list, with descriptions for what
the
commendations are for, and the awarded commendations are in separate
tables.

The Commendations table has 5 fields: CommendationName(txt/PK),
CommendationDescription(memo), CommendationMedal(oleObject),
CommendationRibbon(oleObject), and Ribbon(Yes/No). The COmmendationMedal
and
CommendationRibbon fileds hold images of what the medal looks like and if
it
has an accompanying ribbon, what it looks like too. The ribbon field is
used
to determine if the commendation has a ribbon version.

The Awarded Commendations table has 3 fields: RebelID(txt/FK),
Commendation(txt/FK), and CommendationReason(memo). All together is the
PK.
This table holds all the soldiers commendations and the reasons for
awarding
them.

OK. Now, on the tab with the subform that shows all of a soldier's
commendations there is a command button that is used to add a
commendation.
When pressed it opens a new form that has 6 information fields. They are
all
unbound. The first is a listbox bound to a query that populates it with
all
the available commendations. The next is a textbox that displays the
description of the commendation when it is clicked on in the list box.
there
are 2 bound object frames for the images of the selected commendation, a
textbox that holds the RebelID of the current open soldier and a final
textbox that is for entering the reason for awarding the commendation. I
populate all the controls except for the RebelID and Reason for awarding
textboxes with the update event of the listbox. I pass the RebelID to its
textbox when the form is opened.

The user can do the following things in the form: Click on a commendation
in
the list box, enter text in the reason for awarding textbox, click the
confirm commendation cmd button, and click the close form cmd button. The
problem I am having is with the code to add the commendation to the
awarded
commendations table that is attached to the click event of the confirm
commendation cmd button.

The code I got from Ofer in another thread (thanks!). When I click on the
confirm commendation button I get the error message in the subject line. I
have ensured that the Microsoft DAO 3.6 Object Library is checked in the
references. Here is the code:

Private Sub cmd_confirmcommendation_Click()
On Error GoTo Err_cmd_confirmcommendation_Click

Dim MyDb As Database, Myrec As Recordset
Set MyDb = CodeDb ' Current MDB
Set Myrec = MyDb.OpenRecordset("Select * From AwardedCommendations
where
RedelID = " & Me.[txt_id])

Myrec.AddNew
Myrec!RebelID = Me.txt_id
Myrec!Commendation = Me.[lst_commendation]
Myrec!CommendationNotes = Me.[txt_reason]
Myrec.Update

Me.txt_reason = ""
Me.lst_commendation.SetFocus

Exit_cmd_confirmcommendation_Click:
Exit Sub

Err_cmd_confirmcommendation_Click:
MsgBox Err.Description
Resume Exit_cmd_confirmcommendation_Click

End Sub

Thanks in advance for the help.

Damon

 

[Douglas J. Steele]

What version of Access are you using?

Assuming it's Access 2000 or newer, and you have not removed the reference
to ADO, change your declaration to

Dim MyDb As Database, Myrec As DAO.Recordset

If you have references to both ADO and DAO, you'll find that you'll need to
"disambiguate" certain declarations, because objects with the same names
exist in the 2 models. To guarantee an ADO recordset, you'd use Dim rsCurr
As ADODB.Recordset.

The list of objects with the same names in the 2 models is Connection,
Error, Errors, Field, Fields, Parameter, Parameters, Property, Properties
and Recordset

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

OK, let me clarify things. Here is my current incarnation of the code:

Private Sub cmd_confirmcommendation_Click()
On Error GoTo Err_cmd_confirmcommendation_Click

Dim MyDb As Database, Myrec As Recordset
Set MyDb = CurrentDb() ' Current MDB
Set Myrec = MyDb.OpenRecordset("Select * From AwardedCommendations")

Myrec.AddNew
Myrec!RebelID = Me.txt_id
Myrec!Commendation = Me.[lst_commendation]
Myrec!CommendationNotes = Me.[txt_reason]
Myrec.Update

Me.txt_reason = ""
Me.lst_commendation.SetFocus

Exit_cmd_confirmcommendation_Click:
Exit Sub

Err_cmd_confirmcommendation_Click:
MsgBox Err.Description
Resume Exit_cmd_confirmcommendation_Click

End Sub

Now, I have made changed the datatype for RebelID back to number but I
didn't make it autonumber. I placed breakpoints throughout the code to
find
out when the Type Mismatch error was occuring. It would occur when the Set
MyRec line executed. Hope that helps you help me

Damon

I also took Ofer's suggestion and got rid of the criteria part of the sql
statement, so now I just have: Set Myrec = MyDb.OpenRecordset("Select *
From
AwardedCommendations")

Actually, I realized that the field doesn't need to be number (I had it
set
to autonumber) So I changed it to text, but I am still getting a type
mismatch error.

:

The database type of RebelID is number. How do I put the value of a
text box
into a number datatype? I am now getting a Type Mismatch error.

By the way, yours and Ofer's and Camaro69's help is awesome. Thank
you.

:

Is that a literal cut-and-paste? If so, you've got RedelID = in the
query,
not RebelID =

If your query actually has the field name spelled correctly, what's
the
database type of RebelID? If it's text, you need to put quotes
around the
value you're using:

Set Myrec = MyDb.OpenRecordset( _
"Select * From AwardedCommendations where " & _
" RebelID = " & Chr$(34) & Me.[txt_id] & Chr$(34))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Hi there. I have found a few posts with this problem but their
solutions
did
not work for me.

My DB is for recording the personnel of a ficticious military. I
am
building
forms for entering all the information. My main form consists of
a Tab
group
which separates the ifnormation by category. One of the tabs
displays the
commendations awarded to a specific soldier/record. the
commendations are
displayed as a contiuous subform. Whereas almost all my details
for a
soldier
is held in one table, the commendation list, with descriptions
for what
the
commendations are for, and the awarded commendations are in
separate
tables.

The Commendations table has 5 fields: CommendationName(txt/PK),
CommendationDescription(memo), CommendationMedal(oleObject),
CommendationRibbon(oleObject), and Ribbon(Yes/No). The
COmmendationMedal
and
CommendationRibbon fileds hold images of what the medal looks
like and if
it
has an accompanying ribbon, what it looks like too. The ribbon
field is
used
to determine if the commendation has a ribbon version.

The Awarded Commendations table has 3 fields: RebelID(txt/FK),
Commendation(txt/FK), and CommendationReason(memo). All together
is the
PK.
This table holds all the soldiers commendations and the reasons
for
awarding
them.

OK. Now, on the tab with the subform that shows all of a
soldier's
commendations there is a command button that is used to add a
commendation.
When pressed it opens a new form that has 6 information fields.
They are
all
unbound. The first is a listbox bound to a query that populates
it with
all
the available commendations. The next is a textbox that displays
the
description of the commendation when it is clicked on in the list
box.
there
are 2 bound object frames for the images of the selected
commendation, a
textbox that holds the RebelID of the current open soldier and a
final
textbox that is for entering the reason for awarding the
commendation. I
populate all the controls except for the RebelID and Reason for
awarding
textboxes with the update event of the listbox. I pass the
RebelID to its
textbox when the form is opened.

The user can do the following things in the form: Click on a
commendation
in
the list box, enter text in the reason for awarding textbox,
click the
confirm commendation cmd button, and click the close form cmd
button. The
problem I am having is with the code to add the commendation to
the
awarded
commendations table that is attached to the click event of the
confirm
commendation cmd button.

The code I got from Ofer in another thread (thanks!). When I
click on the
confirm commendation button I get the error message in the
subject line. I
have ensured that the Microsoft DAO 3.6 Object Library is checked
in the
references. Here is the code:

Private Sub cmd_confirmcommendation_Click()
On Error GoTo Err_cmd_confirmcommendation_Click

Dim MyDb As Database, Myrec As Recordset
Set MyDb = CodeDb ' Current MDB
Set Myrec = MyDb.OpenRecordset("Select * From
AwardedCommendations
where
RedelID = " & Me.[txt_id])

Myrec.AddNew
Myrec!RebelID = Me.txt_id
Myrec!Commendation = Me.[lst_commendation]
Myrec!CommendationNotes = Me.[txt_reason]
Myrec.Update

Me.txt_reason = ""
Me.lst_commendation.SetFocus

Exit_cmd_confirmcommendation_Click:
Exit Sub

Err_cmd_confirmcommendation_Click:
MsgBox Err.Description
Resume Exit_cmd_confirmcommendation_Click

End Sub

Thanks in advance for the help.

Damon

 

[Damon]

"Dim MyDb As Database, Myrec As DAO.Recordset" - Fixed my problem. Thanks a
bunch!

What version of Access are you using?

Assuming it's Access 2000 or newer, and you have not removed the reference
to ADO, change your declaration to

Dim MyDb As Database, Myrec As DAO.Recordset

If you have references to both ADO and DAO, you'll find that you'll need to
"disambiguate" certain declarations, because objects with the same names
exist in the 2 models. To guarantee an ADO recordset, you'd use Dim rsCurr
As ADODB.Recordset.

The list of objects with the same names in the 2 models is Connection,
Error, Errors, Field, Fields, Parameter, Parameters, Property, Properties
and Recordset

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

OK, let me clarify things. Here is my current incarnation of the code:

Private Sub cmd_confirmcommendation_Click()
On Error GoTo Err_cmd_confirmcommendation_Click

Dim MyDb As Database, Myrec As Recordset
Set MyDb = CurrentDb() ' Current MDB
Set Myrec = MyDb.OpenRecordset("Select * From AwardedCommendations")

Myrec.AddNew
Myrec!RebelID = Me.txt_id
Myrec!Commendation = Me.[lst_commendation]
Myrec!CommendationNotes = Me.[txt_reason]
Myrec.Update

Me.txt_reason = ""
Me.lst_commendation.SetFocus

Exit_cmd_confirmcommendation_Click:
Exit Sub

Err_cmd_confirmcommendation_Click:
MsgBox Err.Description
Resume Exit_cmd_confirmcommendation_Click

End Sub

Now, I have made changed the datatype for RebelID back to number but I
didn't make it autonumber. I placed breakpoints throughout the code to
find
out when the Type Mismatch error was occuring. It would occur when the Set
MyRec line executed. Hope that helps you help me

Damon

I also took Ofer's suggestion and got rid of the criteria part of the sql
statement, so now I just have: Set Myrec = MyDb.OpenRecordset("Select *
From
AwardedCommendations")

:

Actually, I realized that the field doesn't need to be number (I had it
set
to autonumber) So I changed it to text, but I am still getting a type
mismatch error.

:

The database type of RebelID is number. How do I put the value of a
text box
into a number datatype? I am now getting a Type Mismatch error.

By the way, yours and Ofer's and Camaro69's help is awesome. Thank
you.

:

Is that a literal cut-and-paste? If so, you've got RedelID = in the
query,
not RebelID =

If your query actually has the field name spelled correctly, what's
the
database type of RebelID? If it's text, you need to put quotes
around the
value you're using:

Set Myrec = MyDb.OpenRecordset( _
"Select * From AwardedCommendations where " & _
" RebelID = " & Chr$(34) & Me.[txt_id] & Chr$(34))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Hi there. I have found a few posts with this problem but their
solutions
did
not work for me.

My DB is for recording the personnel of a ficticious military. I
am
building
forms for entering all the information. My main form consists of
a Tab
group
which separates the ifnormation by category. One of the tabs
displays the
commendations awarded to a specific soldier/record. the
commendations are
displayed as a contiuous subform. Whereas almost all my details
for a
soldier
is held in one table, the commendation list, with descriptions
for what
the
commendations are for, and the awarded commendations are in
separate
tables.

The Commendations table has 5 fields: CommendationName(txt/PK),
CommendationDescription(memo), CommendationMedal(oleObject),
CommendationRibbon(oleObject), and Ribbon(Yes/No). The
COmmendationMedal
and
CommendationRibbon fileds hold images of what the medal looks
like and if
it
has an accompanying ribbon, what it looks like too. The ribbon
field is
used
to determine if the commendation has a ribbon version.

The Awarded Commendations table has 3 fields: RebelID(txt/FK),
Commendation(txt/FK), and CommendationReason(memo). All together
is the
PK.
This table holds all the soldiers commendations and the reasons
for
awarding
them.

OK. Now, on the tab with the subform that shows all of a
soldier's
commendations there is a command button that is used to add a
commendation.
When pressed it opens a new form that has 6 information fields.
They are
all
unbound. The first is a listbox bound to a query that populates
it with
all
the available commendations. The next is a textbox that displays
the
description of the commendation when it is clicked on in the list
box.
there
are 2 bound object frames for the images of the selected
commendation, a
textbox that holds the RebelID of the current open soldier and a
final
textbox that is for entering the reason for awarding the
commendation. I
populate all the controls except for the RebelID and Reason for
awarding
textboxes with the update event of the listbox. I pass the
RebelID to its
textbox when the form is opened.

The user can do the following things in the form: Click on a
commendation
in
the list box, enter text in the reason for awarding textbox,
click the
confirm commendation cmd button, and click the close form cmd
button. The
problem I am having is with the code to add the commendation to
the
awarded
commendations table that is attached to the click event of the
confirm
commendation cmd button.

The code I got from Ofer in another thread (thanks!). When I
click on the
confirm commendation button I get the error message in the
subject line. I
have ensured that the Microsoft DAO 3.6 Object Library is checked
in the
references. Here is the code:

Private Sub cmd_confirmcommendation_Click()
On Error GoTo Err_cmd_confirmcommendation_Click

Dim MyDb As Database, Myrec As Recordset
Set MyDb = CodeDb ' Current MDB
Set Myrec = MyDb.OpenRecordset("Select * From
AwardedCommendations
where
RedelID = " & Me.[txt_id])

Myrec.AddNew
Myrec!RebelID = Me.txt_id
Myrec!Commendation = Me.[lst_commendation]
Myrec!CommendationNotes = Me.[txt_reason]
Myrec.Update

Me.txt_reason = ""
Me.lst_commendation.SetFocus

Exit_cmd_confirmcommendation_Click:
Exit Sub

Err_cmd_confirmcommendation_Click:
MsgBox Err.Description
Resume Exit_cmd_confirmcommendation_Click

End Sub

Thanks in advance for the help.

Damon