append query question (1 Viewer)

[shrndegruv]

Hi all

I was wondering how I can pass parameters to an append query. I have the query set up to enter rows into a table, but I need to be able to pass it parameters.

Thoe code below executes when an apartmentunit is created. Basically I am attaching a Kitchen, Bathroom, some other rooms, and a variable number of bedrooms to each unit.

For each one of these rooms, I need to be able to attach WindowConditions. Ive commented where my questions are.

    Dim uNumber As Integer
    Dim nBedrooms As Integer
    
    uNumber = Me.UnitNumber
    nBedrooms = Me.NumberBedrooms
        
    DoCmd.SetWarnings False

    DoCmd.OpenQuery "appAddGeneral"
    DoCmd.OpenQuery "appAddKitchen"
    DoCmd.OpenQuery "appAddBathroom"
    DoCmd.OpenQuery "appAddLivingDiningFoyer"

    Select Case nBedrooms
    Case 1
        DoCmd.OpenQuery "appAddBedroom"
    Case 2
        DoCmd.OpenQuery "appAddBedroom"
        DoCmd.OpenQuery "appAddBedroom"
    End Select


    'All the above works fine -- rows are being created as appropriate


    'here's where the trouble is.  Its giving an error on the marked line    
    Dim DB As DAO.Database
    Dim QDef As DAO.QueryDef
    Set DB = CurrentDb
    Set QDef = CurrentDb.QueryDefs!appWindowCondition
    
---Error    QDef.Parameters![RoomType] = "Kitchen"
    QDef.Execute
    
--  Now I dont know if I need to add something to my appendquery. RoomType is a field in the WindowCondition table, but I didnt specify, and I dont know how, to specify, to enter a value for the roomtype.

    DoCmd.SetWarnings True

Also, how do I keep track, or get the ID field value created in each append query? I need to insert that as well. So after I execute the appAddBathroom query, I need to get the ID (which is an autonumber) of the row I just created, and insert it into the WindowCondition row I am going to create.

Thanx in advance

 

[shrndegruv]

can anyone offer any help?

 

[Mile-O]

QDef.Parameters(0) = "Kitchen")

 

[shrndegruv]

SJ I dont think that will work -- I think the problem was I never told the append query to have a field Roomtype


anyway, instead of using append query Ive gone to just doing SQL.

Set rec = DB.OpenRecordset("WindowCondition")
    
    With rec
        .FindFirst "UnitNumber = " & uNumber
        If Not .NoMatch Then
            cID = !ID
        End If
    End With
        
    DoCmd.RunSQL "INSERT INTO WindowCondition(RoomID, UnitNumber, RoomType)" _
                & "VALUES (" & cID & ", " & uNumber & ", 'Kitchen')"

Is there a better way to get the ID of the room I just created? Also, my Insert isn't working....