Refresh Table Data Based On ComboBox

[t0ny84]

Cross Post Links
https://www.mrexcel.com/board/threads/refresh-table-data-based-on-combobox.1178079/
https://www.access-programmers.co.uk/forums/threads/refresh-table-data-based-on-combobox.318905/
https://www.accessforums.net/showthread.php?t=84133&p=480705#post480705

I am asking this question on multiple forums as I'm not sure what is the best forum to get assistance with my question. It will also hopefully see assistance from different users which should give different solutions (multiple learning opportunities).


Hi,

Sorry in advance if this doesn't make sense!

I am hoping someone can assist, I am currently building a Microsoft Access Database for my workplace to keep track of employees and their movements (e.g. Team Changes). I am trying to create an Employee Details Form like in the Student \ Time Card Microsoft Access Templates where I use a combo box to select from a list and once an employee is selected their details load in the fields on the form. I have tried importing this form and updating the source to my query but am unable to get the Embedded Macro to work. I am wondering if someone can please either explain how the Combo Box on the Templates are setup\work or alternatively what code (either Macro or Embedded Macro) could I use from scratch to have my form do the same.

Thanks in advance,
t0ny84

[Bob Fitz]

Can you post a copy of your db

[t0ny84]

Hey Bob,

Here is a trimmed down version including the Table, Query and Form (The fields are a bit chopped up as it wouldn't allow me to upload a file over 500kb).

Thanks in advance for any and all assistance.

t0ny84

[Welshgasman]

Well thank you for posting the cross post links.
Might want to alert the other sites as well though?

[t0ny84]

Well thank you for posting the cross post links.
Might want to alert the other sites as well though?

Hey Welshgasman can confirm I did this when I created the new posts on each relevant forum.

[t0ny84]

Hey Bob,

Here is a trimmed down version including the Table, Query and Form (The fields are a bit chopped up as it wouldn't allow me to upload a file over 500kb).

Thanks in advance for any and all assistance.

t0ny84

[Welshgasman]

Yes, just seen that on AWF ��

[Bob Fitz]

t0ny84

I think the attached db does what you require but it uses VBA rather than Macros. Hope this helps

[t0ny84]

Hey Bob,

Thank you so much for your prompt assistance, this looks to work perfectly for this situation. Having the code also I should be able to customise for other parts of this project.

A very big thank you again!

t0ny84

[Bob Fitz]

Hey Bob,

Thank you so much for your prompt assistance, this looks to work perfectly for this situation. Having the code also I should be able to customise for other parts of this project.

A very big thank you again!

t0ny84

I'm glad that you found it of use.
I would encourage you to use code rather than macros.
Any macros that you have in a form/report can be converted onto code by Access. There's a command on the design ribbon.

[t0ny84]

I'm glad that you found it of use.
I would encourage you to use code rather than macros.
Any macros that you have in a form/report can be converted onto code by Access. There's a command on the design ribbon.

Hey Bob, can you confirm in sub Form_Load() the line
DoCmd.GoToRecord , "", acNewRec

should take the form to a new record (open blank?)?

If so do you know what would be stopping it from not working, when I run your version it always takes me to the first employee in the list.

[t0ny84]

I believe I might have solved it:

Original Private Sub Form_Load

Private Sub Form_Load()


On Error GoTo Form_Load_Err


    If (IsNull(OpenArgs)) Then
        Exit Sub
    End If
 DoCmd.GoToRecord , "", acNewRec

Edited Private Sub Form_Load

Private Sub Form_Load()


DoCmd.GoToRecord , "", acNewRec
' Commented out below DoCmd and added it here.


On Error GoTo Form_Load_Err


    If (IsNull(OpenArgs)) Then
        Exit Sub
    End If
' DoCmd.GoToRecord , "", acNewRec <- Commented this line out.

[Bob Fitz]

t0ny84

The code in the form's Load event is original code as you posted.
The first three lines ask if the form's OpenArgs property is null and if that is true the code exits the sub.
The following line, which moves to a new record, is only reached and run IF the form's OpenArgs property has some value.

[t0ny84]

Hi Bob,

With

DoCmd.GoToRecord , "", acNewRec

after the first three lines it always loads the form with the first available record where as with it before the first three lines it opens blank on a new record. Would I be correct in assuming that my method makes these lines of code redundant?

[Bob Fitz]

Hi Bob,

.....where as with it before the first three lines it opens blank on a new record......

That could only be true it it were opened from some event code or macro that supplied a value to the form's OpenArgs property.
If the form is only ever opened directly from the Navigation pane then none of the code after the first three lines will ever run.
If all you want is for the form to always open at a new record then the only code you require is:

DoCmd.GoToRecord , "", acNewRec