Append Query - Single record

[airman]

Hi,
I am trying to run an append query, but instead of appending the whole table, I would like to only append a single record.

I have an append query, and it works like a charm. I can also get it to work with prompting the user for a parameter (in my case LeaseId which is a primary key). When the user is prompted and enters the LeaseId it only appends the single matching record. Works like a charm.

However, I don't want to prompt the user for the paramater. I want the user to generate the LeaseId based on the selections in two combo boxes.

So far the I do get the right LeaseId, but I have no idea how to actually pass the LeaseId to the query. I've tried the following, but I know it's completely wrong.

stDocName = "approveLease"
    DoCmd.OpenQuery stDocName, acNormal, acEdit, "[LeaseId]=" & Me.buildingCombo.Column(0)

Any ideas on how to actually accomplish this?

 

[KenHigg]

Do all of this in the query. Don't try to pass anything when you call it. Like, ref the combo boxes in the query itself.

Make sense?

 

[Mile-O]

Something like:

INSERT INTO MyTable (MyField)
SELECT OtherField 
FROM OtherTable
WHERE BuildingField = Forms!FormName!buildingCombo;

 

[airman]

Thanks for the quick response. It makes sense to me to include the condition right in the Query (since I'm only going to be running it from a single form, with a single record option).

However, first of all, I didn't even know that I could use SQL for the query (although it is pretty obvious to me that that's what queries are based on lol), and second I can't seem to get the syntax right.

This is what I have so far:

INSERT INTO approvedLeases ( LeaseId, Landlord, Tenant, [Building Name], Unit, [Size], [Least Term], [Start Date], [End Date], [Renwal Option], [Notice Date], [Renewal Terms], [Year1 Rent/SQ/FT], [Year1 Rent Monthly], [Year1 TMI/sqf], [Year1 TMI monthly], [Year2 Rent/SQ/FT], [Year2 Rent Monthly], [Year2 TMI/sqf], [Year2 TMI monthly], [Year3 Rent/SQ/FT], [Year3 Rent Monthly], [Year3 TMI/sqf], [Year3 TMI monthly], [Year4 Rent/SQ/FT], [Year4 Rent Monthly], [Year4 TMI/sqf], [Year4 TMI monthly], [Year5 Rent/SQ/FT], [Year5 Rent Monthly], [Year5 TMI/sqf], [Year5 TMI monthly], [Heat Included in TMI], [Hydro Included in TMI], [Use of Property], [Emergency Contact], [Emergency Number], [Insurace Policy], [Insurance Expiring], [Insurance Agent], [Insurance Telephone], Other, buildingName )
SELECT Leases.LeaseId, Leases.Landlord, Leases.Tenant, Leases.[Building Name], Leases.Unit, Leases.Size, Leases.[Least Term], Leases.[Start Date], Leases.[End Date], Leases.[Renwal Option], Leases.[Notice Date], Leases.[Renewal Terms], Leases.[Year1 Rent/SQ/FT], Leases.[Year1 Rent Monthly], Leases.[Year1 TMI/sqf], Leases.[Year1 TMI monthly], Leases.[Year2 Rent/SQ/FT], Leases.[Year2 Rent Monthly], Leases.[Year2 TMI/sqf], Leases.[Year2 TMI monthly], Leases.[Year3 Rent/SQ/FT], Leases.[Year3 Rent Monthly], Leases.[Year3 TMI/sqf], Leases.[Year3 TMI monthly], Leases.[Year4 Rent/SQ/FT], Leases.[Year4 Rent Monthly], Leases.[Year4 TMI/sqf], Leases.[Year4 TMI monthly], Leases.[Year5 Rent/SQ/FT], Leases.[Year5 Rent Monthly], Leases.[Year5 TMI/sqf], Leases.[Year5 TMI monthly], Leases.[Heat Included in TMI], Leases.[Hydro Included in TMI], Leases.[Use of Property], Leases.[Emergency Contact], Leases.[Emergency Number], Leases.[Insurace Policy], Leases.[Insurance Expiring], Leases.[Insurance Agent], Leases.[Insurance Telephone], Leases.Other, Leases.buildingName
FROM Leases
WHERE [LeaseId]=Forms!LeaseOffer!buildingCombo.Column(0);

If I do it this way, when running the query I get an undefined function error for the last line. I tried puting it in quotes, but still no luck. As you can tell, I pretty new to SQL and I think now my main problem is just syntax.

Once again thanks for the quick responses.

 

[KenHigg]

You can certainly learn a lot from building this from scratch in sql but why not just do it in the query builder grid display?

 

[airman]

Do you mean in Design View? I tried it, but I don't know what to put in for the criteria. When I put in under the

Field: LeaseId
Criteria: Forms!LeaseOffer!buildingCombo.column(0)

I still get my undefined function error. (Undefined function Forms!LeaseOffer!buildingCombo.column(0))

So I know its my syntax, hence why I tried to play around in SQL. How would I be able to enter this into the criteria box?

Again thanks for the quick responses.

 

[KenHigg]

Forms!LeaseOffer!buildingCombo.column(0)

Should be:

Forms!LeaseOffer!buildingCombo

???

 

[KenHigg]

Wait, there shouldn't be a 'criteria'...

Instead, just place the ref to cbo box on the top row of the grid intsead of on the criteria row.

 

[airman]

I was doing the Forms!LeaseOffer!buildngCombo.column(0) because the combo box has two columns, Column(0)= LeaseId, Column(1)= buildingName unit #.... (Column(1) was a work around I had to do and talked about in this thread: http://www.access-programmers.co.uk/forums/showthread.php?t=86584 )

Because of the workaround that I did, I could use either the LeaseId or the buildingName (ie. column(0) or column(1)), however when I try just to put it in the criteria section, I still get a prompt. If I manually type in the comboboxes value it works, but the whole point of the combobox is that the user won't have to type anything...

I hope all that makes sense. BTW, if I'm using the Forms!LeaseOffer!buildingCombo without the column, I have to use it in the buildingName criteria since the comobox is bound to column 1.

 

[airman]

When I place it in the top row (ie Field), I still get a prompt.

I'm not sure if it's even possible to filter an append query without user input. Maybe if I make a temp table, that will only store one field and have the form populate that field, then run the query and use the temp table to filter it? This is getting way too complicated.

 

[KenHigg]

I have to split - No worry, what you're trying to do is simple, just hard to explain in a forum setting... I'll check back later if no one else picks up on this thread

 

[airman]

Thanks Ken,
In the mean time, I'll keep working on it

 

[airman]

Well, it's official, I am a moron lol

Ok, I got it working. I did end up using it in the criteria field for LeaseId, however this time I got the name of my Form right lol

So in the LeaseId criteria, I simply entered: [forms]![Lease Offer]![buildingCombo]

and it works perfectly. (Notice the space in [Lease Offer])


Anyways, once again thankyou SJ McAbney and special big thanks to Ken.

Until next problem...

Eric