Enter Parameter Value

[weircolin]

Hi there

I have a query which has the following in the SQL

INSERT INTO Atendee ( Person, Event )
SELECT FirstName+' '+LastName AS Person, Forms!eventgenerate!
cboselectevent AS Event
FROM [Members Names];

When the form containing cboselectevent is open and I use the combo
box to select a value, when I press the button to run this query I get
is asking to enter the parameter value for Forms!eventgenerate!
cboselectevent. Does anyone have a solution? It was working fine,
but decided to stop now!

Cheers

Colin

 

[Marshall Barton]

I have a query which has the following in the SQL

INSERT INTO Atendee ( Person, Event )
SELECT FirstName+' '+LastName AS Person, Forms!eventgenerate!
cboselectevent AS Event
FROM [Members Names];

When the form containing cboselectevent is open and I use the combo
box to select a value, when I press the button to run this query I get
is asking to enter the parameter value for Forms!eventgenerate!
cboselectevent. Does anyone have a solution? It was working fine,
but decided to stop now!

First, double check that you spelled the name of the form
and the combo box correctly.

If that's not the problem, then we need to know how you are
running the query. If you used RunSQL, it should not
prompt. If you used Execute, then you need to set the
query's parameter value in the code.

 

[Michael Gramelspacher]

Hi there

I have a query which has the following in the SQL

INSERT INTO Atendee ( Person, Event )
SELECT FirstName+' '+LastName AS Person, Forms!eventgenerate!
cboselectevent AS Event
FROM [Members Names];

When the form containing cboselectevent is open and I use the combo
box to select a value, when I press the button to run this query I get
is asking to enter the parameter value for Forms!eventgenerate!
cboselectevent. Does anyone have a solution? It was working fine,
but decided to stop now!

Cheers

Colin

I guess all you questions go back to your thread "Managing a list of events" on 16 Aug 2008.

I think you have tables Persons, Events and Attendees.

Attendees is a relationship which has the two keys person_id and event_id.

You may have a query Members Names with is perhaps SELECT person_id,
[first_name] & " " & [last_name] AS Person FROM Persons ORDER BY
last_name, first_name;

so the insert query would perhaps run in a form event so:

Dim s as String.


s = "INSERT INTO Attendees (person_id, event_id) " & _
"SELECT person_id, " & me.cboSelectEvent & " FROM [Members Names] " & _
WHERE NOT EXISTS (SELECT * FROM Attendees AS a " & _
"WHERE a.person_id = [Members Names].person_id AND a.event_id = " & me.cboSelectEvent & ");"

' the Where Not Exists is intended to prevent inserting duplicates and prevent a 3022 error

CurrentDB.Execute s, dbFailOnError

here is something similar to look at. I think it works:
http://www.psci.net/gramelsp/temp/EventsAttendance.zip

 

[weircolin]

Hi

I have gotten rid of it saying that now, was being silly and was
telling the query to get the value from a form, but in this instance
it was opperating as a sub form. Rectified that, however another saga
unfolds...

I now get a warning message up:

"Microsoft Access Can't append all the records in the append query"

Says it set x no. field to null due to a type conversion failure.
Gives other ones but says that they are all 0, so their not the
issue.

Asks if I want to run the action query anyway or ignore the errors.

When I open the form to display the data, it has added the number of
records to the table as expected, but all their names are blank.

Any thoughts?

Colin

 

[John W. Vinson]

Hi

I have gotten rid of it saying that now, was being silly and was
telling the query to get the value from a form, but in this instance
it was opperating as a sub form. Rectified that, however another saga
unfolds...

I now get a warning message up:

"Microsoft Access Can't append all the records in the append query"

Says it set x no. field to null due to a type conversion failure.
Gives other ones but says that they are all 0, so their not the
issue.

Asks if I want to run the action query anyway or ignore the errors.

When I open the form to display the data, it has added the number of
records to the table as expected, but all their names are blank.

It would appear that you're trying to set the value of a Number field (perhaps
a Lookup field, which is actually a numeric foreign key...???) to a Text
value.

 

[weircolin]

It would appear that you're trying to set the value of a Number field (perhaps
a Lookup field, which is actually a numeric foreign key...???) to a Text
value.
--

             John W. Vinson [MVP]- Hide quoted text -

- Show quoted text -

Looking at it now, yes I am. It works ok when selecting a name
however. Is there anyway I can fix that?

 

[John W. Vinson]

Looking at it now, yes I am. It works ok when selecting a name
however. Is there anyway I can fix that?

By storing the numeric value (the actual content of the field) rather than the
text. If you would like help doing so please post your code and the SQL of the
append query.

 

[weircolin]

That would be great!

Not sure what code you require however.

This is the quey SQL

INSERT INTO Atendee ( Person, Event )
SELECT FirstName+' '+LastName AS Person, Forms![Add New Event]!
eventgenerate!cboselectevent AS Event
FROM [Lanarkshire Links members];

Thanks

Colin

 

[John W. Vinson]

That would be great!

Not sure what code you require however.

This is the quey SQL

INSERT INTO Atendee ( Person, Event )
SELECT FirstName+' '+LastName AS Person, Forms![Add New Event]!
eventgenerate!cboselectevent AS Event
FROM [Lanarkshire Links members];

Thanks

Colin

The Attendee table *DOES NOT CONTAIN* a text field into which you can insert
FirstName + ' ' + LastName. At least it shouldn't contain such a field.

It may *appear* to contain such a field, if you've fallen victim to the
infamous Lookup Wizard's mischief.

What the Attendee table actually contains is *A NUMBER* - a link to the Person
table.

Try

INSERT INTO Atendee (Person, Event)
SELECT Person.PersonID, FOrms![Add New
Event]!eventgenerate.Form!cboSelectEvent
FROM [Lanarkshire Links Members];

This is making some possibly unwarrented assumptions about the structure of
your tables and forms, but in essence you need to insert a numeric ID, not a
name.