Prefix + AutoNumber as Item Code

[GinaWhipp]

1. I do everything via VBA (code)
2. Do you understand how your Macro works?
3. There is a free version of SQL Server... SQL Server Express

 

[johannaellamay]

1. I do everything via VBA (code)
2. Do you understand how your Macro works?
3. There is a free version of SQL Server... SQL Server Express

1. I see. All Macros can be converted to VBA right? Do you suggest that I just convert or just stick with Macros?

2. Yes, I guess. I just didn't understand what you said. Hmm. I honestly am satisfied with my t_AuditTrail. I just need a button in f_EmployeeMultiple where I can generate a report that contains historical data (immediate supervisor, department, start and end dates of contract, etc.) per employee. Do you suggest that I just make a historical report that shows all fields per employee? Or is it better to separate for example the button for previous departments, immediate supervisor, etc.? So then I'll have one button per historical data?

3. Hmm. I will research on that! Thank you very much!

 

[GinaWhipp]

1. Stick with what you know...

2. Hmm, whichever works better for you. I don't use a report, I instead allow that information to viewed on screen.

 

[johannaellamay]

1. Stick with what you know...

2. Hmm, whichever works better for you. I don't use a report, I instead allow that information to viewed on screen.

1. Yeah. I'm quite happy with Macros for now.

2. How exactly do you do that?

 

[GinaWhipp]

Almost 4:00 am here... will post tomorrow or rather later today!

 

[johannaellamay]

Hey. So I made queries for all the fields that I want to filter out for historical data. All of which are taken from t_AuditTrail. After making the query, I went to make a report. Then I tried to add a button on f_HRMSCommonUser so I can open a report (say Job Title history) but only show data where [atRecordID] (in the report) is equal to [eEmployeeID] (in the form).

Problem is, it doesn't work. I tried using this for the Where Condition in my Macro:

="[atRecordID]=" & [eEmployeeID]

It doesn't work. It either sends my syntax error or makes me enter a parameter value. I also tried to do it in VBA:

DoCmd.OpenReport "r_AuditTrail_eJobTitle",acViewReport,,"[atRecordID]=" & [eEmployeeID]

This code also gives me the same error.

I'm just stuck. What I only want is for the user to be able to see historical data that matches the Employee. The formula works for other reports except this. -_- I'm getting really frustrated.

 

[GinaWhipp]

WOW! I just got this notification today...

Are those ID fields numeric or text?

 

[johannaellamay]

WOW! I just got this notification today...

Are those ID fields numeric or text?

Yes, they're both numeric. eEmployeeID is taken from t_Employee white atRecordID is from t_AuditTrail.

 

[GinaWhipp]

Hmm, don't know why I didn't spot this to begin with, look at the example below and make the appropriate changes...

DoCmd.OpenReport "YourReport", acViewPreview, , "[FieldInReport]=" & Me![ControlOnForm]

 

[johannaellamay]

Hmm, don't know why I didn't spot this to begin with, look at the example below and make the appropriate changes...

DoCmd.OpenReport "YourReport", acViewPreview, , "[FieldInReport]=" & Me![ControlOnForm]

It didn't work. This is my code:

Private Sub Command809_Click()
    DoCmd.OpenReport "r_AuditTrail_eJobTitle", acViewPreview, , "[atRecordID]=" & Me![eEmployeeID]
End Sub

It says:

Run-time error '3075':
Syntax error (missing operator) in query expression '[atRecordID]='.

 

[GinaWhipp]

Is the NAME of your Control on your Form?

Me![eEmployeeID]

Or the name of the Control Source? And... is there an Employee ID there or is it empty?

 

[johannaellamay]

Is the NAME of your Control on your Form?

Me![eEmployeeID]

Or the name of the Control Source? And... is there an Employee ID there or is it empty?

Yes, it's the name of my control on my form. It is also the name of the control source. And there's no data yet. So I guess it's empty. Is eEmployeeID the problem? Or atRecordID?

 

[GinaWhipp]

Yes, that is the problem... it's empty. You will need to write code for it to ignore that line is the Control Source is empty.

 

[johannaellamay]

Yes, that is the problem... it's empty. You will need to write code for it to ignore that line is the Control Source is empty.

Private Sub Command809_Click()

    If Me.eEmployeeID = "" Then
    End If
    
    DoCmd.OpenReport "r_AuditTrail_eJobTitle", acViewPreview, , "[atRecordID]=" & Me![eEmployeeID]

End Sub

Is this correct? Apparently not because it's still giving me the same error.

This also didn't work:

Private Sub Command809_Click()

    If Me.eEmployeeID = "" Then
    MsgBox "Cannot open report without Employee Data", vbInformation, "No data"
    End If
        
    DoCmd.OpenReport "r_AuditTrail_eJobTitle", acViewPreview, , "[atRecordID]=" & Me![eEmployeeID]

End Sub

And when I try to debug it, vba highlights this:

    DoCmd.OpenReport "r_AuditTrail_eJobTitle", acViewPreview, , "[atRecordID]=" & Me![eEmployeeID]

 

[GinaWhipp]

Hmm, not quite, it will still print, try...

If Me.eEmployeeID = "" Then Exit Sub

DoCmd.OpenReport "r_AuditTrail_eJobTitle", acViewPreview, , "[atRecordID]=" & Me![eEmployeeID]

 

[johannaellamay]

Hmm, not quite, it will still print, try...

If Me.eEmployeeID = "" Then Exit Sub

DoCmd.OpenReport "r_AuditTrail_eJobTitle", acViewPreview, , "[atRecordID]=" & Me![eEmployeeID]

Still didn't work. It gave me the same error.

Run-time error '3075':
Syntax error (missing operator) in query expression '[atRecordID]='.

 

[johannaellamay]

View attachment HR_X v5.1 - Copy.zip

Here's my progress so far.

 

[GinaWhipp]

I will look later today... it's 2:40 am where I am!

Side note, I will be away all next week. While I will have access to eMail it will be sporadic so you might be waiting a bit for answers.

 

[johannaellamay]

I will look later today... it's 2:40 am where I am!

Side note, I will be away all next week. While I will have access to eMail it will be sporadic so you might be waiting a bit for answers.

Oh no. Haha. It's fine. But would it be okay if you could take a look at it before you go MIA? Just so I have something to work with. I'm really quite stuck.

 

[GinaWhipp]

Yes, tomorrow or rather later today... trying to hit the sack a wee bit tired to be looking over a database.