Macros for a web database data entry form

[Twinnie]

I am a newbie to Ms Access and I am sure this is a simple task for the professionals:

I am currently designing a web database (using MicrosoftAccess 2010) that i wish to publish via Microsoft Sharepoint.
There are 3 tables:
Table1: Level_table


Table2:UserDetails
Table3:Submission

The Table 1 and Table 2 are linked via the primary key of Level_table(Level_ID) which forms the foreign key on UserDetails table.

The form called User_Input contains 2 textboxes (Firstname andSurname)-txtFirstname,txtSurname, and 5 combo boxes (Level, standard, bronze,silver, gold)-cboLevel,cboStd,cboBr,cboSil and cboGold.

The table called UserDetails contain the following fields:User_ID-primarykey,Name, Designation,Level_Type,Level_ID-foreign key

The table Level_table contains the following fields:Level_ID (primary key),Level_Type

I have successfully populated the combo boxes with their respective selectionsbut I have been unable to populate the submission table with entries from theform using Macros.

The User_Input form is expected to save its entries to the submission tablebased on the headings (similar to the controls on the form):
ID (Autonumber),Firstname, Surname, Level, standard, bronze, silver, gold.

I have successfully done this using VBA but VBA is not compatible with AccessServices for publishing to Sharepoint and hence the need to use Macros to get this to work.

Thank you.

[June7]

The User_Input form is bound to Submission table?

I don't understand the 5 comboboxes. What data is saved - Yes/No? Can a user have all five? If a user can be only one of the 5 then should be a single field and a single combobox bound to that field.

The SetValue method is used in macros to programmatically save data to the form current record.

[Twinnie]

Thanks a lot June 7,

The User_Input form is bound to the Submission table via the property sheet function called RecordSource with the table stated there.

The data in the 5 combo boxes are names of some staff and the different combo signifying Standard, Bronze, Silver and Gold are different award categories.

The level combobox groups the names in the other comboboxes into levels based on the cboLevel selection (this is working well).

The issue is that when i click the submit button on the form, the submission table is not populated with the entries from the form.

Thank you.

The User_Input form is bound to Submission table?

I don't understand the 5 comboboxes. What data is saved - Yes/No? Can a user have all five? If a user can be only one of the 5 then should be a single field and a single combobox bound to that field.

The SetValue method is used in macros to programmatically save data to the form current record.

[June7]

What is the RowSource of each combobox? Each of the five comboboxes are bound to field? Is this field a number type? Are you saving StaffID? Is StaffID a number value? Each of the 5 fields will always have a value for each record?

[Twinnie]

Thank you June 7.

Please find response below:

cboLevel:SELECT Level_Table.Level_Type FROM Level_Table;
cboStd:SELECT [UserDetails].[User_Name] FROM UserDetails WHERE ([UserDetails].[Level_Type]=[forms]![User_Input]![cboLevel]);
cboBr:SELECT [UserDetails].[User_Name] FROM UserDetails WHERE ([UserDetails].[Level_Type]=[forms]![User_Input]![cboLevel]);
cboSil:SELECT [UserDetails].[User_Name] FROM UserDetails WHERE ([UserDetails].[Level_Type]=[forms]![User_Input]![cboLevel]);
cboGold:SELECT [UserDetails].[User_Name] FROM UserDetails WHERE ([UserDetails].[Level_Type]=[forms]![User_Input]![cboLevel]);

What is the RowSource of each combobox? Each of the five comboboxes are bound to field? Is this field a number type? Are you saving StaffID? Is StaffID a number value? Each of the 5 fields will always have a value for each record?

[June7]

So you want to save user name not a user ID? This is a text value and fields are text type?

I don't know much about web database and SharePoint but in regular db I would not reference the subform in the RowSource.

SELECT User_Name FROM UserDetails WHERE Level_Type=[cboLevel];

[Twinnie]

the combo boxes are working well. the only challenge I have currently is how to populate my table 'submission' with the form inputs.

[June7]

Are the comboboxes bound to the fields? What is their ControlSource property? Bound controls should automatically save value to record.

[Twinnie]

Thanks so much June7.

After I updated the ControlSource property, it worked.

There is yet another problem: All submissions from the form only submits to the first row of the Submission table.

Hence, the subsequent submissions keep replacing the previous submissions on the same first row.

Are the comboboxes bound to the fields? What is their ControlSource property? Bound controls should automatically save value to record.

[June7]

Must move to new record row for entering a new record.

[Twinnie]

Hello June7,

I presume I am asking very simple questions, kindly bear with me please.

The problem I have is that each time i fill the form and click the submit button, the first row of the table "Submission" is filled with the entries; entries do not go into the next vacant row i.e it replaces the first row with new entries.

Is there a macro I need to enable (on the Submission table or User_Input form )to ensure that entries are saved on the next available row on the Submission table.

Thanks for the assistance.

Must move to new record row for entering a new record.

[June7]

On the form, first move to new record row then enter data. This should result in a new record in the table.

Can set the form to open only to new record row by setting the DataEntry property to Yes. Then existing records will not be available for viewing or editing.

Record entry/edit is committed to table when move to another record or close form or run code.

[Twinnie]

Thanks a lot June7.

In this context, you are a life saver.

I am grateful.

Please how do I communicate that this has been solved?

Thank you.

On the form, first move to new record row then enter data. This should result in a new record in the table.

Can set the form to open only to new record row by setting the DataEntry property to Yes. Then existing records will not be available for viewing or editing.

Record entry/edit is committed to table when move to another record or close form or run code.

[June7]

Thread Tools dropdown above first post.