Can't write to query

[knowshowrosegrows]

Hello,

I have a simple form where I want the user to fill in 4 controls.
tblCensusEvent.CensusDate, tblCensusEvent.Census, tblCensusEvent.Admiss,
tblCensusEvent.[D/C], and for that data to go into the query.

This little form is attached to the following query:

SELECT tblCensusEvent.CensusDate, tblCensusEvent.Census,
tblCensusEvent.Admiss, tblCensusEvent.[D/C], qryCurrentCapacity.CensusCap
FROM tblCensusEvent LEFT JOIN qryCurrentCapacity ON
tblCensusEvent.Program_ID = qryCurrentCapacity.Program_ID;

The query qryCurrentCapacity referenced above is the following query:

SELECT tblProgram.Program_ID, Sum(qryRunningCapacity.Cap) AS CensusCap
FROM qryRunningCapacity INNER JOIN tblProgram ON
qryRunningCapacity.Program_ID = tblProgram.Program_ID
WHERE (((qryRunningCapacity.CapEndDate) Is Null))
GROUP BY tblProgram.Program_ID;

Not surprisingly, I guess, my little form will not let me add a record to
tblCensusEvent for a new date.

Can anyone understand my question and make a suggestion?

 

[Carl Rapson]

It looks like you just need to display a value from qryCurrentCapacity, so
instead of joining your table to qryCurrentCapacity use an unbound text box
control and use DLookUp to fill in the value:

(in Form_Current event)

Me.CensusCap =
DLookUp("CurrentCap","qryCurrentCapacity","qryCurrentCapacity.Program_ID=" &
Me.Program_ID)

That should make the query underlying the form updatable. Of course, use
your own field and control names.


Carl Rapson

 

[knowshowrosegrows]

I'm getting a compile Error Syntax Error with the following code:

Private Sub Form_Current()
Me.CapLookUp =
DLookUp("CensusCap","qryCurrentCapacity","qryCurrentCapacity.Program_ID=" &
Me.Program_ID)

End Sub
--
Thanks

You all are teaching me so much

It looks like you just need to display a value from qryCurrentCapacity, so
instead of joining your table to qryCurrentCapacity use an unbound text box
control and use DLookUp to fill in the value:

(in Form_Current event)

Me.CensusCap =
DLookUp("CurrentCap","qryCurrentCapacity","qryCurrentCapacity.Program_ID=" &
Me.Program_ID)

That should make the query underlying the form updatable. Of course, use
your own field and control names.


Carl Rapson

Hello,

I have a simple form where I want the user to fill in 4 controls.
tblCensusEvent.CensusDate, tblCensusEvent.Census, tblCensusEvent.Admiss,
tblCensusEvent.[D/C], and for that data to go into the query.

This little form is attached to the following query:

SELECT tblCensusEvent.CensusDate, tblCensusEvent.Census,
tblCensusEvent.Admiss, tblCensusEvent.[D/C], qryCurrentCapacity.CensusCap
FROM tblCensusEvent LEFT JOIN qryCurrentCapacity ON
tblCensusEvent.Program_ID = qryCurrentCapacity.Program_ID;

The query qryCurrentCapacity referenced above is the following query:

SELECT tblProgram.Program_ID, Sum(qryRunningCapacity.Cap) AS CensusCap
FROM qryRunningCapacity INNER JOIN tblProgram ON
qryRunningCapacity.Program_ID = tblProgram.Program_ID
WHERE (((qryRunningCapacity.CapEndDate) Is Null))
GROUP BY tblProgram.Program_ID;

Not surprisingly, I guess, my little form will not let me add a record to
tblCensusEvent for a new date.

Can anyone understand my question and make a suggestion?

 

[ntc]

the query is not updateable due to ambiguity....

consider sourcing your form on a table or at least a simpler, updateable
query; and using subforms;

essentially using subforms to help link/align the data in different
tables/queries rather than via nested joins in one big query as you now
have.....

 

[John W. Vinson]

I'm getting a compile Error Syntax Error with the following code:

Private Sub Form_Current()
Me.CapLookUp =
DLookUp("CensusCap","qryCurrentCapacity","qryCurrentCapacity.Program_ID=" &
Me.Program_ID)

End Sub

If that's what's actually in your code, you're getting an error because you
have split one line into three. Everyghing from Me. through the close
parenthesis should be on the same line, or else you should use a line
continuation signal: a blank followed by an underscore at the end of the line:

Private Sub Form_Current()
Me.CapLookUp = _
DLookUp("CensusCap","qryCurrentCapacity","qryCurrentCapacity.Program_ID=" & _
Me.Program_ID)

End Sub

This can happen when you copy code from a newsgroup post (usually limited to
50 or 60 characters) into VBA code (not so limited).