Solved Setting Combobox Value With VBA MS Access

[lamisamuel]

I am trying to make a module to pass a single result of a query into a Combobox to be populated/displayed immediately on Form_Load event, but I keep getting the following error: Run-time error '2465' "Microsoft Access can't find the field 'MyCombo' referred to in your expression" Query result is tested and returning the proper value. the problem is in the reference call to the MyCombo combobox.

This is my code below:

Public Function getSetRefNo() As String
Dim rs1 As DAO.Recordset
    
Dim currentformName As String
currentformName = Screen.ActiveForm.Name
Dim docidrefnoquery As String
Dim dociddefaultvalue As String
      
Set rs1 = CurrentDb.OpenRecordset("SELECT DISTINCT ColA FROM TblA WHERE ColC = " & Forms(currentformName)![Combo25])
    
Do Until rs1.EOF = True
    docidrefnoquery = rs1(0)
    rs1.MoveNext
Loop
rs1.Close
Set rs1 = Nothing

dociddefaultvalue = DLookup("RefNo", docidrefnoquery) 'RefNo here is the target column in the Query

Forms(currentformName)![MyCombo] = dociddefaultvalue 'MyCombo here is the Combobox Name
Debug.Print docidrefnoquery & " - " & dociddefaultvalue
End Function

on the targeted form, I use this code:

Private Sub Form_Load()
    Call getSetRefNo
End Sub

after opening the targeted form, I receive the above mentioned error. I don't know what's wrong I tried to trace everything and it seems to be fine, I used the same chunk of codes in other places and worked fine. don't know what's wrong here to be honest. I would be grateful if you could help me elaborate what's going on.
It's worth noting that instead of calling the function inside the form load, I tried putting the whole code inside the form's Load event, and the code would work fine and give proper result only if I define the form's name statically, or use Me.MyCombo = dociddefaultvalue instead.

 

[arnelgp]

because it is in the Load event, Screen.ActiveForm is not what you expect (not the current form).
it will be the "previous" form or Null if there is non.

better to "pass" the name of the Form or the Form object itself to the function.

 

[lamisamuel]

because it is in the Load event, Screen.ActiveForm is not what you expect (not the current form).
it will be the "previous" form or Null if there is non.

better to "pass" the name of the Form or the Form object itself to the function.

thank you for such quick response!
may I ask how? I am not too good with VBA and it gets confusing really fast.
thanks in advance.

 

[Gasman]

Have you walked though your code?
This is a mainform and not a subform I am assuming?

You could pass the form object and use that in the sub?

 

[arnelgp]

it is "plain" info.
Screen.ActiveForm.Name, refers to the "visible" form on the "screen".
since your form is still Loading (and you are using Load event), the form isn't visible yet.

you call getSetRefNo on the Timer event of the form, where it is fully visible.

 

[lamisamuel]

Have you walked though your code?
This is a mainform and not a subform I am assuming?

You could pass the form object and used that in the sub?

it's not a subform, but this form is opened as popup & modal after clicking a textbox on a previous form before it, to open it where id=id

 

[lamisamuel]

it is "plain" info.
Screen.ActiveForm.Name, refers to the "visible" form on the "screen".
since your form is still Loading (and you are using Load event), the form isn't visible yet.

can I use the form.timer event to delay the function execution until form is fully loaded?

 

[arnelgp]

can I use the form.timer event

yes, that is the last event where the form is fully shown.
just immediately kill the timer (Me.TimerInterval = 0), when you
enter the timer event.

 

[Gasman]

I have no idea as to how many records there would be in rs1, but why not just move last?

 

[MajP]

IMO this code makes no sense.
It is written as a generic function, but it is not. All the combo box names are hard coded in the code. You loop a recordset for no reason. I think you are trying to set a default value of myCombo based on a selection in combo25. How could this work in the load event. What are you really trying to do. This code is not it.

 

[lamisamuel]

yes, that is the last event where the form is fully shown.
just immediately kill the timer (Me.TimerInterval = 0), when you
enter the timer event.

I tried this and I get the same error.

Private Sub Form_Load()
Me.TimerInterval = 3000
Call getSetRefNo
End Sub

Private Sub Form_Timer()
Me.TimerInterval = 0
End Sub

why it's not working properly?
values are validated and everything's fine except defining the Combobox display value. I am so confused since yesterday.

 

[lamisamuel]

I have no idea as to how many records there would be in rs1, but why not just move last?

query only returns one row of two columns, i try to get the second column value and set it to MyCombo as it's default value to display on load

 

[lamisamuel]

IMO this code makes no sense.
It is written as a generic function, but it is not. All the combo box names are hard coded in the code. You loop a recordset for no reason. I think you are trying to set a default value of myCombo based on a selection in combo25. How could this work in the load event. What are you really trying to do. This code is not it.

sorry for any inconvenience.
I just want to have some tables that I insert some data into manually, then retrieve such data dynamically in code whatever the used form is. I used similar code to perform other functions and it worked like a charm. this is just weird for me and I am not too good with MS Access.

 

[arnelgp]

Private Sub Form_Load()
Me.TimerInterval = 30
End Sub

Private Sub Form_Timer()
Me.TimerInterval = 0
Call getSetRefNo
End Sub

 

[lamisamuel]

Private Sub Form_Load()
Me.TimerInterval = 30
End Sub

Private Sub Form_Timer()
Me.TimerInterval = 0
Call getSetRefNo
End Sub

wow. it worked :')
I am so HAPPY. Thanks for making my day, arnelgp!
now I got what you meant in your former comment, " just immediately kill the timer (Me.TimerInterval = 0), when you enter the timer event"
a huge THANK YOU again!

 

[Gasman]

sorry for any inconvenience.
I just want to have some tables that I insert some data into manually, then retrieve such data dynamically in code whatever the used form is. I used similar code to perform other functions and it worked like a charm. this is just weird for me and I am not too good with MS Access.

Nothing like trying to run, before you can walk.
Set a breakpoint on the first line of executable code in that sub, then walk the code line by line using F8 and inspect all the values.

 

[Pat Hartman]

If you are trying to pass the FK value to a popup form, the following is the way to do it. Using the form's load event to populate the FK as you are doing is 100% wrong. If you add a second record, the FK will not be populated and the record will be orphaned.

Pass the PK value in the OpenArgs property of the OpenForm method. THEN In the BeforeInsert event of the popup, set the FK value:

Me.SomeFKfield = Me.OpenArgs

Using this method you never dirty a record before the user types into it AND every record that gets added by the popup gets the correct FK assigned.


I'm guessing that part of your confusion is caused because you are using table level lookups so you can't tell if you are referencing a text field or the ID field. So, remove the table level lookups. They are just a crutch anyway and get in the way once you write VBA or use queries.

 

[lamisamuel]

If you are trying to pass the FK value to a popup form, the following is the way to do it. Using the form's load event to populate the FK as you are doing is 100% wrong. If you add a second record, the FK will not be populated and the record will be orphaned.

Pass the PK value in the OpenArgs property of the OpenForm method. THEN In the BeforeInsert event of the popup, set the FK value:

Me.SomeFKfield = Me.OpenArgs

Using this method you never dirty a record before the user types into it AND every record that gets added by the popup gets the correct FK assigned.


I'm guessing that part of your confusion is caused because you are using table level lookups so you can't tell if you are referencing a text field or the ID field. So, remove the table level lookups. They are just a crutch anyway and get in the way once you write VBA or use queries.

no no, I am just trying to modify a record by its ID into another popup form, no other insertions whatsoever.
now everything is working as expected after arnelgp's proposed solution.
thanks everyone for your feedback and your input on the matter.

 

[Pat Hartman]

What is there left to say except welcome aboard.

 

[lamisamuel]

What is there left to say except welcome aboard.

this forum has answered many of my questions already before I registered today, such a great and helpful committee here
thank you, Pat!