thanks for response.

this is in a desktop database .mdb
Access 2000 9.0 version

Mateen

Mateen-

OK, then using linked tables means you can treat it as though it is an all-mdb setup.

The best way to do this is to remove the WHERE clause from your SELECT statement and provide a WhereCondition when you open the form bound to that query. The form that has the command button to open the form dbo_assignments6 should also have three controls - one each for ContractNo, StartDate, and EndDate. When the user clicks the command button, code in the Click event should examine the three controls and build a filter string - basically a WHERE clause but without the WHERE keyword. Something like this:

Dim strWhere As String

  If IsNull(Me.ContractNo) Then
    MsgBox "You must specify a Contract Number."
    Exit Sub
  Else
    strWhere = "approvals.contractno = " & Me.ContractNo
  End If
  If IsNull(Me.StartDate) _
    Or (Not IsDate(Me.StartDate)) Then
    MsgBox "You must enter a start date."
    Exit Sub
  Else
    strWhere = strWhere & _
      " AND aenddate >= #" & Me.StartDate & "#"
  End If
  If IsNull(Me.EndDate) _
    Or (Not IsDate(Me.EndDate)) Then
    MsgBox "You must enter an end date."
    Exit Sub
  Else
    If Me.StartDate > Me.EndDate Then
      MsgBox "Start date cannot be greater than end date."
      Exit Sub
    End If
    strWhere = strWhere & _
      " AND aenddate <= #" & Me.EndDate & "#"
  End If
  DoCmd.OpenForm "dbo_assignments6", WhereCondition:=strWhere

I do not understand your second question. Why do you think you need to insert rows? Your sample INSERT SQL is attempting to insert the values "empno", "aposit", "astrdate", "aenddate", "cshift", and "acontract" into a single new row. Are you wanting to copy records from another contract or date range?


John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"

thanks for response.

I use the following query in select statement without where clause, form dbo_assignments6 bound following query.

SELECT dbo_employees.empno, dbo_employees.name, dbo_approvals.contractno, dbo_assignments.aposit, dbo_assignments.acontract, dbo_assignments.astrdate, dbo_assignments.aenddate,dbo_assignments.cshift FROM (dbo_employees INNER JOIN dbo_approvals ON dbo_employees.empno=dbo_approvals.empno) INNER JOIN dbo_assignments ON dbo_employees.empno=dbo_assignments.empno

form dbo_assignments6 working fine. it display all data.

in another form I create button open form. and on the click event procedure I use your coding.

but when I click the button it give error on first row ie. Me.contractno that
" compile error : Method or data member not found "


I defined contractno ie

Dim Contractno as String

but error is same.

Actually, I make program on employees records. our employees working different contracts. when contract finish,
management assign new contract to that employees. I prepare monthly report that one contract how many employees
are working, ie report for the month of October. 10/1/2004 to 10/31/2004 20 employees are working.
for next month ie same report will make, but only I have to change the dates etc. rest of the data will remain
same. I want that when I prepare report for the next month I retrieve records of previous month, and do some changing, ie dates
and again insert the records into the tables.

Yes I have to insert multiple rows. (not single row ) how can insert multiple row by the form ?
ie one contract many employees are working, multiple row retrieve from the tables.
and insert multiple rows into the tables. how ?

data retrieve from the table as posted above.

regards.

Mateen

Mateen-

In my previous reply, I said: "The form that has the command button to open the form dbo_assignments6 should also have three controls - one each for ContractNo, StartDate, and EndDate." Did you do that? Me.ContractNo in the code is an attempt to reference the ContractNo control mentioned above. You can give the control any name you like, but the code needs to reference that name. The same holds true for Me.StartDate and Me.EndDate.

As for your second problem, the sample data you posted earlier appears to contain a start date and end date. If an employee is assigned to a project for another month, it would seem to me you would want to simply update the end date rather than copy the row. If an employee is assigned to a new project how would you know which rows to copy? And what is the Primary Key of this table?


John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"