Textbox needs to show query results

[skydivetom]

Experts:

I need to return the results of a query in a form's textbox. Allow me to provide some background first:
- The query below returns one record (two fields)
- That is, right now, criteria for [T01_StaffMembers.StaffMemberIDpk] = 6
- Thus, record returns [T01_StaffMembers.StaffMemberIDpk] = 6 and [T01_StaffMembers_1.StaffMemberIDpk] = 1454

Query (in SQL):

SELECT T01_StaffMembers.StaffMemberIDpk, T01_StaffMembers_1.StaffMemberIDpk
FROM T01_StaffMembers INNER JOIN T01_StaffMembers AS T01_StaffMembers_1 ON T01_StaffMembers.StaffMemberIDpk = T01_StaffMembers_1.[BackfillStaffMemberIDfk]
WHERE (((T01_StaffMembers.StaffMemberIDpk)=6));

Now, I the form, I have a combo for the first field: [T01_StaffMembers.StaffMemberIDpk]. Let's call it Combo1. Additionally, I have a textbox called "Textbox2".


So, basically, once I select a value (e.g., 6) from Combo1, I want Textbox2 to display 1454.

Having changed the criteria from 6 to Combo1, what's the VBA I need to use for displaying the query results in Textbox2?

Query (for VBA):

SELECT T01_StaffMembers.StaffMemberIDpk, T01_StaffMembers_1.StaffMemberIDpk
FROM T01_StaffMembers INNER JOIN T01_StaffMembers AS T01_StaffMembers_1 ON T01_StaffMembers.StaffMemberIDpk = T01_StaffMembers_1.[BackfillStaffMemberIDfk]
WHERE (((T01_StaffMembers.StaffMemberIDpk)=Combo1));

Thank you in advance,
EEH

[Bob Fitz]

If you use the query as the Row Source setting for combo 1 you can include the data you require in the second control as a hidden column in the first combo

[skydivetom]

Bob:

Thanks for the assist... Combo1 has a rowsource that, e.g., displays 1000 records (ID numbers).

Once I pick an ID from Combo1, I want that ID to be the query criteria and thus show the corresponding value in Textbox2.

That said, given that I already a rowsource in Combo1, I'm not entirely clear how to implement your solution. Any additional thoughts?

Thanks,
Tom

[ArviLaanemets]

Not entirely sure what you want to get, but probably you get an idea about easier way from here.

[skydivetom]

Arvil:

Thanks for posting the sample db. Not entirely certain if that's following my logic. Maybe it is... I will review further.

However, just prior to posting your sample db, I also worked on a sample one. Please see attached zipped file.

Process:
- When you open the form, select a name from the top combo.
- Then click "Move Staff Member" command button
- A message box will appear indicating either "No backfill assigned!" or "A backfill has been assigned!".

For right now, only staff member "Bryant, Tony" has a backfill. Once I select his name and press the command button, I then want to display a "9" (ID number for Roger Walker -- see T01_StaffMembers) in the form.
For anyone else, the 2nd textbox should be blank.

How do I tweak my VBA to accomplish this concept?

Thanks,
Tom

[accesstos]

Hi Tom!

I didn't see the attached file but, generally, you can retrieve the value of a hidden column of a combobox with this expression as Control Source of textbox:

=[combo1].column(2)

for the third column.

Set the Row Source of the combo1 to

SELECT StaffMemberIDpk, StaffMemberName, BackfillStaffMemberIDfk FROM T01_StaffMembers ORDER BY StaffMemberName;

and set the Column count to 3 and the Column widths to 0;5;0

No need VBA or query statement. As you change the selected member from the combo1, the textbox display the value of the third hidden column (BackfillStaffMemberIDfk).

Try it.

Cheers,
John

Edit: Sorry Tom!
You are looking for the opposite.

Just type in the textbox:

=DLookUp("StaffMemberIDpk","T01_StaffMembers","BackfillStaffMemberIDfk=" & [cboStaffMembers])

[skydivetom]

Correction:

John - your proposed solution works GREAT!!!


Thousand thanks for thinking outside the box!


Now, I just need an IF statement which either does a) when there's value in the textbox vs. b) does something else when there's no value.


I think that should be easy... we'll see.


Again, thanks for the help!

Tom

[accesstos]

You are welcome Tom, but, I was ready to post:

No Tom! It's Ok!
I can open your file and now I did.

You have a "parent-child" relationship between StaffMemberIDpk and BackfillStaffMemberIDfk. That is clear.
Next you want to find if there is a record with the current StaffMemberIDpk in the BackfillStaffMemberIDfk field, and if there is, to display its StaffMemberIDpk in NewID textbox. Am I right?

If I am, i suggest you three ways.

1 -- With in Control Source Dlookup:
You keep in NewID the expression:

=DLookUp("StaffMemberIDpk","T01_StaffMembers","BackfillStaffMemberIDfk=" & Nz([cboStaffMembers],0))

2 -- With recordset in VBA:

Set rs = CurrentDb.OpenRecordset("SELECT T01_StaffMembers.StaffMemberIDpk FROM T01_StaffMembers " _
                                     & "WHERE (((T01_StaffMembers.BackfillStaffMemberIDfk)=" _
                                     & nz(Me.StaffMemberIDfk_Archive,0) & "));", dbOpenSnapshot)
    Me.NewID = Null
    If rs.RecordCount > 0 Then
        Me.NewID = rs!StaffMemberIDpk
    End If

3 -- With dlookup in VBA:

Me.NewID = DLookup("StaffMemberIDpk", "T01_StaffMembers", "BackfillStaffMemberIDfk=" & nz(Me.StaffMemberIDfk_Archive,0))

and as common for all ways in button's code:

If IsNull(Me.NewID) Then
    MsgBox "No backfill assigned!"
Else
    MsgBox "A backfill has been assigned!"
End If

So, I think that now I cover you.

Cheers,
John

[skydivetom]

John:


I'm still muddling over your solution. Until "fully digested", please allow me to ask a follow-up question.


Upon opening the form, the control "BackfillStaffMemberIDfk" shows an "#Error". Once only I change the combo cboStaffMembers, it is then when it's changing to either a null value or a corresponding ID value.

In the event I want to take action on the first record w/o changing the value, my IF/THEN statement fails.


Currently, I have the following:

If IsNull(Me.BackfillStaffMemberIDfk) Then
MsgBox "No backfill assigned!"
'... some other code


Else

MsgBox "A backfill has been assigned!"
'... some other code

End If

So, if I were to not change the cboStaffMembers upon opening the form and then click on a command button, I would want to go into the If statement; however, right now, it jumps into the Else statement. And then it fails.


My question: What is the proper IF statement that checks for both null values and #Errors?

Thanks,
Tom

[accesstos]

Tom, I have update the code snippets in my post #8. Using the NZ() function solves the problems.

[skydivetom]

I replaced the Dlookup with your proposed Dlookup =DLookUp("StaffMemberIDpk","T01_StaffMembers","Bac kfillStaffMemberIDfk=" & Nz([cboStaffMembers],0)).
However, I'm still struggling w/ opening the form with the first record which has a backfill. Until I click the combo, there's no value in the BackfillStaffMemberIDfk.
I tried to include the Dlookup in the form's opening but it indicates that I cannot use that method.
Any final thoughts on how I ensure that the Dlookup will immediately updated (based on the StaffMemberIDpk) when opening the form?
Thanks,
Tom

[accesstos]

Sorry Tom, but your question confuses me.

What have to display the "BackfillStaffMemberIDfk" textbox (NewID in your attachment) before the selection of the first staff member in combobox?

The expression in the textbox runs automatically at the loading of the form. You don't have to do anything for that. Normally, you see nothing in the text box before select something in the combobox.

If you choose the first suggested way, you have to ensure that the value of the textbox doesn't change by the code of the button.

cheers,
John

[skydivetom]

John - thank you. I was able to integrate your methodology.

As always, you've come through again w/ a superb solution.

Cheers,
Tom

[accesstos]

Tom, you are welcome! I am happy to help!

Thank you for your kind words!

Cheers,
John