Adding selections to some sort of table

[shabbaranks]

Hi Guys,



I have created some lists and would like to be able to submit the selected items to a table - is this possible within Access? An image of how my form looks is here

http://imgur.com/rsjRB

So the user selects from the drop down lists, and clicks the add record button which then adds to some sheet or table?

Thanks

[Toyman]

Hi Guys,

I have created some lists and would like to be able to submit the selected items to a table - is this possible within Access? An image of how my form looks is here

http://imgur.com/rsjRB

So the user selects from the drop down lists, and clicks the add record button which then adds to some sheet or table?

Thanks

You can put the following code behind the "on click" event of the button using an append query:

Dim sAnalysis as String
Dim sDept as String
Dim sProject as String
Dim sHours as Integer
Dim Msql as string

sAnalysis = Me.Analysis
sDept = Me.[Administration Dep]
sProject = Me.Project
sHours = Me.Hour

Mysql = "INSERT INTO YourTableName ( Analysis, [Administration Dep], Project, Hour ) SELECT '" & sAnalysis & "' AS Analysis, '" & sDept & "' AS Dept, '" & sProject & "' AS Project, " & sHours & " AS Hour"

DoCmd SetWarnings False
DoCmd RunSql Mysql
DoCmd SetWarnings True

[shabbaranks]

Thanks Toyman, is there a way to display the records which you add to some sort of viewer below. Please excuse my ignorance - I'm not a regular Access user

[shabbaranks]

Ive just realised what I need to do - or at least try and do. If I create a table which will hold the results of the selections in my earlier example - can I display this within the form?

I think that makes more sense?

Thanks

[Toyman]

Ive just realised what I need to do - or at least try and do. If I create a table which will hold the results of the selections in my earlier example - can I display this within the form?

I think that makes more sense?

Thanks

From the looks of your form, you have already bound the form to a table. Why do you need to append the record to another table and display it again withe the record from another form. If your form is already bound to a table, anything you type into the field will be recorded in the table, and can be recalled. You could display the records from another table, but why

[shabbaranks]

I don't think I did bind the form to a table - unless I did it inadvertently? The table I am trying to get them inserted to is the table within the sub-form as per this image

http://i.imgur.com/yzuR9.png

Thanks again, I must owe you a pint by now

[Toyman]

I don't think I did bind the form to a table - unless I did it inadvertently? The table I am trying to get them inserted to is the table within the sub-form as per this image

http://i.imgur.com/yzuR9.png

Thanks again, I must owe you a pint by now

Ok, your sub form should already bound to the table for your sub form. It should be linke "parent/child" to the main form via the primary key for the main form and the foreign key of the sub form table. At the end of the code I gave you, put an additional line of code:
Me!YourSubFormName.Form.Requery
This will append the new record to the table and requery your subform to show the new data

[shabbaranks]

Hi Toyman...

I eventually got around to setting the database up as you said and I get a compile error as below:

Private Sub AddToSheet_Click()
Dim sActivity As String
Dim sDepartment As String
Dim sProject As String
Dim sHours As Integer
Dim Msql As String

sActivity = Me.Activity
sDepartment = Me.Department
sProject = Me.Project
sHours = Me.Hour

Mysql = "INSERT INTO TimesheetTable ( Activity, Department, Project, Hour ) SELECT '" & sActivity & "' AS Activity, '" & sDep & "' AS Department, '" & sProject & "' AS Project, " & sHours & " AS Hour"

DoCmd SetWarnings False
DoCmd RunSql Mysql
DoCmd SetWarnings True
Me!TimesheetTableSubform.Form.Requery
End Sub

Obviously Ive done soemthing wrong but I cant see what - thanks

[shabbaranks]

Hi,

Just as an addition to this, is it possible (Im just having a fumble around Access) when a selection is made on the main form it automatically populates the subform elements? Which would then save the submit button as the content will already be there?

Thanks

[Toyman]

Hi,

Just as an addition to this, is it possible (Im just having a fumble around Access) when a selection is made on the main form it automatically populates the subform elements? Which would then save the submit button as the content will already be there?

Thanks

I would not do that since the fields in your main form is not bound. I would leave the submit button in place. To trouble shoot your error as stated above, please reply with the type of fields for each of the following in your table: Activity, Department, Project, Hour, Date. For example, "Activity" is a text field, etc. Also, you might change the name for the "Date" field as this is a reserved word for the "Date" function in access. You might run into problem trying to use that field name in codes later.

[shabbaranks]

Im just having a look at my options. Is there any negative aspect to bounding the subform with the main form? As you said in your previous post... Just trying to make the best out of all this.

The problem earlier was I didnt have the . in place between Do.Cmd

Thanks again

[Toyman]

Im just having a look at my options. Is there any negative aspect to bounding the subform with the main form? As you said in your previous post... Just trying to make the best out of all this.

The problem earlier was I didnt have the . in place between Do.Cmd

Thanks again

No, this is the most common way of displaying related records. You set up the main form based on the main table. The sub form are bound to related records and linked to the main via the primary/foreign keys of the tables.

[shabbaranks]

I seem to be getting nowhere fast though. When I click the button nothing happens anymore, the page is doing something as its refreshing a field which is bound from the form to the table. But all the other fields "should" be transferred using this code:

Private Sub AddToSheet_Click()
Dim sActivity As String
Dim sDepartment As String
Dim sProject As String
Dim sHours As Double
Dim sDescrip As String
Dim Msql As String

sActivity = Me.Activity
sDepartment = Me.Department
sProject = Me.Project
sHours = Me.Hour
sDescript = Me.Description

Mysql = "INSERT INTO TimesheetTable (Activity, Department, Project, Hours, Description) Values ('" & sActivity & "', '" & sDepartment & "', '" & sProject & "', " & sHours & ", '" & sDescrip & "')"
DoCmd.SetWarnings False
DoCmd.RunSQL Mysql
DoCmd.SetWarnings True
Me!TimesheetTableSubform.Form.Requery
End Sub

[Toyman]

I seem to be getting nowhere fast though. When I click the button nothing happens anymore, the page is doing something as its refreshing a field which is bound from the form to the table. But all the other fields "should" be transferred using this code:

Private Sub AddToSheet_Click()
Dim sActivity As String
Dim sDepartment As String
Dim sProject As String
Dim sHours As Double
Dim sDescrip As String
Dim Msql As String
 
sActivity = Me.Activity
sDepartment = Me.Department
sProject = Me.Project
sHours = Me.Hour
sDescript = Me.Description
 
Mysql = "INSERT INTO TimesheetTable (Activity, Department, Project, Hours, Description) Values ('" & sActivity & "', '" & sDepartment & "', '" & sProject & "', " & sHours & ", '" & sDescrip & "')"
DoCmd.SetWarnings False
DoCmd.RunSQL Mysql
DoCmd.SetWarnings True
Me!TimesheetTableSubform.Form.Requery
End Sub

Can you zip your db and post it here. I will take a look at it

[shabbaranks]

Ok great thanks.