Creating multiple entries on diffetne tables based on a date range (1 Viewer)

[IggySoup]

I realise this is quite an old post but it's exactly what I'm looking for. Kind of.

thread=267483 - I cant post links please dont hate me

I am looking to add work resources to an existing database. Each user has a separate table for their year's work.

What I would like this code to do is add for example a type of work for two weeks. However I would like the admins to be able to do this centrally and apply it to the relevant user's table (and if possible ignore the week ends (we don't work them) The data base is calculating the total hours spent on each project or type of work. Ideally I would like it to be a dropdown

can anybody please help me with this? I would be really grateful if someone could point me in the right direction.

I've not used Access for some time and I'm loving using it again but I'm struggling with this. Any help would be greatly accepted

Thanks

Iggy

 

[theDBguy]

Hi. Were you referring to this thread?

 

[IggySoup]

Yes that's exactly the one - I'm not able to post links yet...

thanks for replying

 

[plog]

Each user has a separate table for their year's work.

What you want to accomplish is made a lot more difficult by your poorly structured tables. Each user should not have their own table, instead you should put a field in your work table that indicates what user each record is for. With a properly structured database this might not require any code at all--you'd just insert data into the table and not need VBA to help you.

Can you post a zip file of your database? Also, indicate which tables will be involved with this process.

 

[IggySoup]

Hi. Thanks for responding.
I'm really annoyed with myself becuase my initial plan was to have one table. The existing database was based on linked tables in excel sheets as my company is over reliant on excel. I had converted the sheets to tables and just thought this was an easier way to complete the task.

I think I will go back to my initial DB and go from there.

Ideally what I want us for managers to allocate work and then staff to update it if they worked on something else on each day. I'm guessing the best way forward is an admin navigation and a user nav page. Ideally separate logins for each user but I'll have a think about that.


Thanks for getting back to me. I think I really like this place already

 

[IggySoup]

Hi I'm wondering if someone would still be willing to assist me with this?

I have restructured my tables to have one big table as suggested but I now cant get the code to run- I'm getting a runtime 3346 code which i believe is to do with the select aspect of the code - can anyone point me inthe right direction please?

Private Sub AllocateResource_Click()
Dim strSQL As String

'As you are entering the first record manually with the end date the sql only addes the dates greater than the first date

'This only works where all fields are filled in - you should probably build in a check to see if the Trainer and the Date are accounted for already
'dlookup("Trainer_Name", "Resourcing", "Trainer_Name = " & [Forms]![Resourcing]![Cmb_Trainer_Name] & " and StartDate = " & [the dates])

'If you can omit data from a field then you will have to breakdown the sql statement to omit the data if null - example for the  field  "Activity" - you would also remove the validation for Null Field

'Validate fields are not Null
If IsNull(Me.Project_Title) Then
      MsgBox "Project Name Missing", vbOKOnly + vbCritical, "Error"
      Me.Cmb_Project_Title.SetFocus
      
      
ElseIf IsNull(Me.Trainer_Name) Then
      MsgBox "Trainer Name Missing", vbOKOnly + vbCritical, "Error"
      Me.Cmb_Trainer_Name.SetFocus

ElseIf IsNull(Me.Project_Title) Then
      MsgBox "Project Name Missing", vbOKOnly + vbCritical, "Error"
      Me.Cmb_Project_Title.SetFocus

ElseIf IsNull(Me.Pfolio) Then
      MsgBox "Portfolio Name Missing", vbOKOnly + vbCritical, "Error"
      Me.cmb_Pfolio.SetFocus

      
ElseIf IsNull(Me.Activity) Then
      MsgBox "Activity Missing", vbOKOnly + vbCritical, "Error"
      Me.Cmb_Activity.SetFocus


ElseIf IsNull(Me.Start_Date) Then
      MsgBox "Start Date Missing", vbOKOnly + vbCritical, "Error"
      Me.cmb_Start_Date.SetFocus

ElseIf IsNull(Me.End_Date) Then
      MsgBox "End Date Missing", vbOKOnly + vbCritical, "Error"
      Me.cmb_End_Date.SetFocus


ElseIf IsNull(Me.cmb_Hours_Spent) Then
      MsgBox "Hours Spent Missing", vbOKOnly + vbCritical, "Error"
    Me.cmb_Hours_Spent.SetFocus

Else
           'build the SQL Statement

      strSQL = "INSERT INTO Resourcing (Start_Date, Trainer_Name,Pfolio, Project_Title,Activity,Hours_Spent"
      If Not IsNull(Me.Cmb_Activity) Then
            strSQL = strSQL & ", Activity"
      End If
      
      strSQL = strSQL & ", Project_Title ) "
      strSQL = strSQL & " SELECT Dates.Date, [Forms]![Resourcing]![CMB_Trainer_Name] AS Expr1, [Forms]![Resourcing]![Cmb_Pfolio] AS Expr2, [Forms]![Resourcing]![Cmb_Project_title] AS Expr3, [Forms]![Resourcing]![Cmb_Team] AS Expr4 Forms]![Resourcing]![Cmb_Activity] AS Expr5 Forms]![Resourcing]![Cmb_Hours_Spent] AS Expr6 ![Cmb_End_date] AS Expr7 "
      
      If Not IsNull(Me.Cmb_Activity) Then
            strSQL = strSQL & " , [Forms]![Resourcing]![Cmb_Activity] AS Expr8"
      End If
      strSQL = strSQL & " , [Forms]![Resourcing]![CMB_Training_Type] AS Expr9 "
      
      strSQL = strSQL & " FROM Dates WHERE (((Dates.Date)>[Forms]![Resourcing]![Start_Date] And (Dates.Date)<=[Forms]![Resourcing]![End_Date]))"
      'Run the SQL Statement
      DoCmd.RunSQL (strSQL)
        
End If
Me.Requery
End Sub

many thanks in advance

Iggy

 

[Gasman]

Fisrtly Debug.Print your strSQL after each amendment to check what you have.

You appear to be missing concatenation in various places.?
You need to concatenate the VALUE of the control, not it's name.?

So

strSQL = strSQL & " SELECT Dates.Date, [Forms]![Resourcing]![CMB_Trainer_Name] AS Expr1,

would become

strSQL = strSQL & " SELECT Dates.Date, '" &  [Forms]![Resourcing]![CMB_Trainer_Name] & "' AS Expr1, "

Where the controls have strings, use ', where they have dates use # and nothing for numbers.

Also if this code in is the actual form being reference you can just use Me.[CMB_Trainer_Name]

I would not use spaces in field/control Names either.? Makes life just that bit easier.

I would just use the VALUES clause as well for the INSERT?

https://docs.microsoft.com/en-us/of...ce/insert-into-statement-microsoft-access-sql

HTH

 

[IggySoup]

I'm sorry I'm not sure I follow

do i have to create a line of code for each bit or just the one as it stands but include the concatenation.

Sorry I'm acting a bit thick today

 

[Gasman]

My last post was just an example. You will need to modify all your strings to be concantenated correctly.

Also you just have

![Cmb_End_date] AS Expr7

that is why I suggested displaying the string after every amendment to slowly get it all correct.

I'd also use the VALUES clause as all your data appears to come from the form? and would be easier to code.?
I'd also have seperate strings for Update,Values and Where strings and then put them together at the end.

HTH