Entering data using datasheet

[svjensen]

To be honest, I am not sure if this should go into the VBA section, but let my try to explain my problem.

Let us say that I have a simple database with two tables (example DB is uploaded here: http://vinther-jensen.dk/access/test.mdb) .

One table holds types of expenses (tblUdgiftstyper) while the other holds some values. Each value is paired with an expensetype.
Along with the value two dates are written to the table; the date/time at which it is entered (DataSat), and the date/time at which the values are no longer relevant (DatoUdloeb).

I have made a simple form, which I intent to use for data input/editing (frmAddValues). I will properbly show this as a datasheet.
When opening the form all expensetypes from tblUdgiftstyper must be loaded to the form, so that the user can enter data for each of them.
I have tried doing this via VBA.

But this is causing my some trouble.
I have tried using two different queries to load the data:

SELECT tblUdgiftsstyper.Udgiftsstype
FROM tblUdgiftsstyper;

and then use this as the forms RecordSource. But when I afterwards loop through the fields from the form I only get the first column.

I have then tried

TRANSFORM '0' AS Udtryk2
SELECT tblUdgiftsstyper.Udgiftsstype
FROM tblUdgiftsstyper
GROUP BY tblUdgiftsstyper.Udgiftsstype
PIVOT Year in ('2010','2011','2012','2013');

Which gives me all expensetypes and loads zeroes as values, but it will not let me edit the data once they are loaded.

What am I doing wrong?

I should mention, that I intent to usethe same form to allow the user to edit data. My idea is to the change the query to let it load the actual data, which can then be edited.

I should also mention, that in the real project I need to update some other values and change the expiration date of the existing values prior to writing the new/edited data.

 

[Galaxiom]

Your first query only has one field.
The second one has an aggregate query which by their nature are not updateable.

If I understand correctly I think you want tblUdgiftsstyper (or a query on that table) as the RowSource of a combobox control in frmAddvalues which has the other table (or a qury on it) as its recordsource.

This combo can be used to display the text but record the uid. The Column Count, Bound Column and ColumnWidths are set to achieve this. Try placing a combo using the wizard which will set it up for you.

BTW. Avoid Year and Value as field names as these are reserved words.

 

[svjensen]

I would prefer to display a datasheet in which the user can enter the data. The advantage with a datasheet is, that he/she can see all available expensetypes, as it might be neccesary to enter data for each of them.
So I am not sure that a combobox will do the trick. Or could it be that I am not seeing its full potential?

My idea was therefore to use a query to fill data (i.e. expensetypes) into the form, and then let the user fill out the rest, and afterwards loop through the data and write it to the table.

Point taken about Year and Value

 

[Galaxiom]

The combo has no effect on the choice of form type. The scenario I described allows the user to choose the Udgiftsstype from a dropdown list on a field in a datasheet form.

However I might be completely misundersanding your requirement.