Question How do I populate subform based on selections from combo?

[shabbaranks]

Hi all..

Im trying to populate a subform "on click" of a button, based on the combo box selections from the initial form. The form looks like this:

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

And the code for the onclick is here:

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

Could anyone be so kind as to tell me what I am doing wrong as I get an error

Compile error - and the DoCmd SetWarnings False is highlighted.

Thanks

 

[JANR]

Add a dot after DoCmd

DoCmd.SetWarnings False

DoCmd.SetWarings True

JR

 

[vbaInet]

Aren't you just trying to create a search form? Why are you inserting?

 

[shabbaranks]

Thanks guys,

Its not a search form, Im trying to get it so the selections are made from the various combo boxes and then displayed in the table below. This is so the user can see whats been submitted - would you say this is a bad way of doing it?

Also I get another error stating:
Invalid use of null


Thanks

 

[shabbaranks]

Oops my bad that error was because I didnt have a value in description. Im now getting the error:

Syntax error in INSERT INTO statement

Im guessing this is because of the link to the table but Im not too sure what it means?

 

[vbaInet]

So basically, the user enters records via the combo boxes and textboxes, clicks Add To Sheet and all the records entered should be displayed in the subform?

You should first of all bind your main form and save changes when the Add To Sheet is saved. Then you can worry about displaying it on the subform later.

Your current method of inserting won't in a multi-user environment.

Have you thought about using a listbox.

 

[shabbaranks]

Sorry I dont really understand - Im not the best when it comes to Access. When you say bind form, do you mean binding the form inputs\combo boxes to fields within a table? If so I thought I did that - can you confirm how to achieve this please?

Thanks

 

[shabbaranks]

Ive found how to bind - the problem being when I bind I dont get the selection options. Also how would the list box be a better approach?

Thanks

 

[vbaInet]

I didn't say it's a better approach. I was just asking if you had considered using a listbox?

Can you answer these questions:

1. How many users will use this form?
2. Is the subform supposed to show only those records entered by the user?

 

[shabbaranks]

ahhhhhh.....

20 or so users will be using it, and yes the subform is supposed to only show the current records being input at that time - am I going A about T on this?

Thanks

 

[vbaInet]

Ok, with a subform you also need two more fields, a UserID and a Date field. For every new record added, you will need to include the user's ID and a Date/Time stamp, then you can apply criteria to the record source (via a query) to say:

[UserID] = SomeID AND [DateField] >= SomeDate

If you were to use a listbox, the listbox won't be bound to any table or query and you can add lines to it in code. So with this approach those extra fields won't be necessary. The only thing is a Listbox is read-only.

 

[shabbaranks]

Great, I had a user ID field which pulls the current logged in user details through. What I am struggling with is the date. I have a date picker which works from the main form but I cant get that pulled through to the subform - is there a way of doing this?

Also I have bound the subform fields to the mainform as here:

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

But it only seems to pull the username field through
Thanks

 

[vbaInet]

Drop a textbox on your main form and in the Load event set the value of Now() to the textbox. Like so:

Me.TextboxName = Now()

So in your query, you can put the following under the Date field:

>= [Forms]![[COLOR=red]FormName[/COLOR]]![[COLOR=red]TextboxName[/COLOR]]

Amend the red bits.

Ensure that your date field includes the Time part as well.

 

[shabbaranks]

Can you see any benefites from bounding the subform compared to using a button to add the records to the table? Thanks

 

[vbaInet]

Bound subform - Access handles the refreshing, loading and saving of data.

 

[shabbaranks]

Another question - sorry.. If I use a bound subform and I select the various options it adds the choices which is great. But when it comes to adding a second selections of choices its just overwriting the previous - is there something I am doing wrong?

Thanks again

 

[vbaInet]

What criteria did you put in the subform's record source?

 

[shabbaranks]

In the risk of sounding stupid - where is the record source setting? I cant seem to find it anywhere :S

 

[vbaInet]

This is a property of a form. A subform is just simply a form housed by the subform control.

Upload a stripped down version of your db and tell me how to replicate the problem.

 

[shabbaranks]

Its all good. Im sorted now thanks