Need to build a preloaded form......

[smorelandii]

Hello all....



I need to build a form that will allow users to enter data and save that data to a table within a SQL Server DB. I am experience with SQL but haven't worked with Access or VB in many years. I need this form to be preloaded with data based off of data in a SQL DB. I created a view in SQL that extracts the preload data but have noticed that Access will not allow me to link to a view..... It wants a table or query only. No problem.... I created queries in Access, through SQL view, to replicate my view in SQL Server. So.... now I have the data set I would like to preload into a form that users can manipulate and save to another table. I've built a form pulling data from my query but the form will not allow me to edit the any fields. Um... Okay..... That kinda defeats the purpose of a form.... LOL.....

Please excuse my ignorance..... I'm simply not experienced with Access. Can anyone point me in the right direction? Am I going down the right path with this or am I completly off? Any and all help would be much apreciated.

Thanks,

-Steve

[smorelandii]

Not sure if this helps but here's what I'm doing in my query. All the form does is call the query. I need users to be able to manipulate the NULL fields below and the save the form into some other table.

SELECT TOP 1
'99' AS fldCellNumber,
NULL AS fldConductivity,
NULL AS fldTemperature,
NOW() AS fldDateTime,
NULL AS fldTempCorr,
1 AS SORT
FROM tblCellsEngSetPoints
UNION ALL
SELECT
A.fldCellNumber,
NULL AS fldConductivity,
NULL AS fldTemperature,
NOW() AS fldDateTime,
IIf(B.HD > NOW() - 3 AND B.HD <= NOW() AND B.HD IS NOT NULL,'Harvested',
IIf(B.CI IS NULL,'Not on-line',
IIf(B.CI < B.CO ='Not on-line',
''))) AS fldTempCorr,
A.fldCellNumber AS SORT
FROM tblCellsEngSetPoints AS A, v_CELL_DATES AS B
WHERE A.fldCellNumber = B.CN AND
MID(fldCellNumber,1,1)=1 AND
MID(fldCellNumber,LEN(fldCellNumber),1) IN (1,3,5,7,9)
UNION ALL
SELECT
A.fldCellNumber,
NULL AS fldConductivity,
NULL AS fldTemperature,
NOW() AS fldDateTime,
IIf(B.HD > NOW() - 3 AND B.HD <= NOW() AND B.HD IS NOT NULL,'Harvested',
IIf(B.CI IS NULL,'Not on-line',
IIf(B.CI < B.CO ='Not on-line',
''))) AS fldTempCorr,
A.fldCellNumber + 1000 AS SORT
FROM tblCellsEngSetPoints AS A, v_CELL_DATES AS B
WHERE A.fldCellNumber = B.CN AND
MID(fldCellNumber,1,1)=1 AND
MID(fldCellNumber,LEN(fldCellNumber),1) IN (0,2,4,6,8)
ORDER BY SORT;

[pbaldy]

Generally a form bound to a table or query can be edited. Your problem is that a UNION query is read-only:

http://allenbrowne.com/ser-61.html

You'll either need to come up with a way to break the data down so it can be viewed and edited, or use unbound methods to update your data. Not knowing the nature of your data, I can't offer any suggestions on different ways of viewing it.

[smorelandii]

Thank you for commenting pbaldy..... I will check out your link and look into what you have recomended. As far as the nature of the data goes..... This form would serve as a data entry form for chemical engineers. They harvest cells every day with probs recording the conductivity, temp, etc.... (I haven't added the other fields yet... just trying to get it to work first) of certain chemicals.

fldCellNumber - 99 is the control cell and represents ideal conditions
odd number cells are harvested first and thus recorded first
even numer cells are recorded last and thus recorded last
(this field needs to be read only in the form)
fldConductivity - measurement of chemical conductivity
(this field needs to be manualy entered in the form)
fldTemperature - measurement of chemical temperature
(this field needs to be manualy entered in the form)
fldDateTime - date of today
(this field needs to be read only in the form)
fldTempCorr - field to display text data

SORT - used to sort data records in logical order
(this field will not actually show on the form)
(cell 99 need always be the first record)
(odd cells need to follow in asc order)
(even cells need to follow in asc order)

I have 4 control rooms..... 4 sets of cells..... the WHERE clause figures that out. I will eventually need 4 forms. They will all be extreamly similar though.

[pbaldy]

I'm experiencing Friday afternoon brain fade. Is the main purpose of the UNION query to create a sort? If so, perhaps we can do that with another method.