By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,490 Members | 900 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,490 IT Pros & Developers. It's quick & easy.

MS Access Append Record

P: 2
Hi all,

I have a problem with a query in access that I cant seem to figure out. I have a form named frmRecruitment with a subform named sfrmParticipant. At the moment I am using an append query to append records from 'sfrmParticipant' to 'frmIntervention' using a button on a main form 'frmRecruitment'.

This append query appends all the records from
'sfrmParticipant' to 'frmIntervention' but I only want to append the record that is displayed as the current record in the form or the last record from tblParticipant into tblIntervention and by using the button on the form append only that very last record.

This is what I have so far:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command31_Click()
  2. DoCmd.RunSQL "INSERT into [tblIntervention]([participantID]) VALUES (participantID)"
  3. End Sub
  4.  
  5. Private Sub Command31_Click() DoCmd.RunSQL "INSERT into [tblIntervention]([participantID]) VALUES (participantID)" End Sub
and yes it appends only the information being displayed or the very last record from tblParticipant into tblIntervention but it prompts me to enter the participantID in order to do it.

How do I modify the append query to only append the form record with out promting me and having to enter the participant ID manually?


Thanks for your help!
hikosj
Jun 6 '08 #1
Share this Question
Share on Google+
3 Replies


Expert Mod 2.5K+
P: 2,545
Hi. Problem is that you are referring to participantID in the VALUES part of the string. If it is a local variable you will need to append its value to the string instead of including its name:

Expand|Select|Wrap|Line Numbers
  1. DoCmd.RunSQL "INSERT into [tblIntervention]([participantID]) VALUES ("  & participantID & ")"
If participantID is a control on your form rather than a local variable you will need to use the me!participantID syntax to refer to it. Either way, you cannot include its name in the string as the RunSQL command will not be able to interpret what it means - hence why it is asking you for a parameter value.

if participantID is a field from another table (and not a control on your form or a local variable) you will need to obtain its value by using DLookup or somesuch, as again its value will not be available to runSQL simply by referring to its name the way you are doing at present.

-Stewart
Jun 10 '08 #2

NeoPa
Expert Mod 15k+
P: 31,186
Please remember ALWAYS to use [ CODE ] tags whenever posting code on this site.
Jun 12 '08 #3

P: 2
Hi. Problem is that you are referring to participantID in the VALUES part of the string. If it is a local variable you will need to append its value to the string instead of including its name:

Expand|Select|Wrap|Line Numbers
  1. DoCmd.RunSQL "INSERT into [tblIntervention]([participantID]) VALUES ("  & participantID & ")"
If participantID is a control on your form rather than a local variable you will need to use the me!participantID syntax to refer to it. Either way, you cannot include its name in the string as the RunSQL command will not be able to interpret what it means - hence why it is asking you for a parameter value.

if participantID is a field from another table (and not a control on your form or a local variable) you will need to obtain its value by using DLookup or somesuch, as again its value will not be available to runSQL simply by referring to its name the way you are doing at present.

-Stewart
I finally figured it out! It took me a while but I finally got it to work.
> I added this to the click event of a button
>
>
Expand|Select|Wrap|Line Numbers
  1.  Dim str As String
  2. > str = Form_frmPresentation.presentationID.Value
  3. > DoCmd.Close
  4. > DoCmd.OpenForm "sfrmAppointmentOne", acNormal, , , , acWindowNormal,
  5. > "Value=" + str
  6. >
  7. >
  8. > 'And then I added this to the on load event of the form getting the variable
  9. >
  10. > Form_frmIntervention.participantID.Value =
  11. > Form_frmParticipant.participantID.Value
  12. > Me.Refresh
Jun 19 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.