Copy result from a Query field to a Form textbox

[Juett]

Hi there,

I have a form that has a textbox called "S1_SN" and I have a query called Qry_AutoPop that works and generates the required information. One of the fields in the query is also called "S1_SN". The query is initiated based on the "Bath_SN" value. The form also has this field. Both form and query take their information from the same source- TblDocuments.

What I would like to have happen is a button on the form that when pressed, runs Qry_AutoPop, copies the result/value from the field "S1_SN" and pastes that value into the form textbox called "S1_SN", using the "Bath_SN" value in the form as the input data source for the query.


I have been trying to use the following code:

Me.S1_SN = DLookup("S1_SN", "Qry_AutoPop", "[Bath_SN] = '" & Me.Bath_SN & "'")

I cannot set the record source of the textbox as the query because I want this to be an optional action the user can perform if they choose to, but can also ignore it as well. There is a reason for this, based on the end user's working practices, essentially meaning that sometimes, textbox "S1_SN" will be new data, and on other occasions, it will be the same data as a previous records based on the "Bath_SN" field.

Currently, the above code doesn't throw up an error, it just does nothing at all after the button is pressed.

Any help would be greatly appreciated.

 

[arnelgp]

what does the qry_autopop do?
your form and qry have same table, how can the query search if its not present?

 

[theDBguy]

Hi. The code looks okay. Can you also post the SQL statement for the query?

 

[isladogs]

What is the datatype of field Bath_SN? Number or Text or …?
The expression uses text delimiters but won't work for numbers as written.
Also I suggest you wrap the Dlookup expression using the Nz function in case of null values

 

[arnelgp]

I think its text, as the op is not encountering any error. nz() also not needed for textbox, it can accept null value.

 

[Juett]

Qry_AutoPop searches all previous records using The "Bath_SN" as the search criteria. It then displays the corresponding results, which includes the field "S1_SN".

I have the query set to display the latest/newest record, and only that record, based on the date field sorted in ascending order.

The query works when run independently. I just want to copy the "S1_SN" value from the result and place it in the textbox of the from (in new record mode) if the button is pressed.

The query sql code is:

SELECT TOP 1 TblQ.[Bath_SN], TblQ.[Visit Date], TblQ.[S1_SN], TblQ.[S2_SN], TblQ.[S3_SN], TblQ.[S4_SN], TblQ.[S5_SN], TblQ.[S6_SN], TblQ.[P1_SN]
FROM TblQ WHERE (((TblQ.[Bath_SN])=[]))
ORDER BY TblQ.[Visit Date] DESC;

 

[isladogs]

I also think its text but it would be helpful if the OP confirmed that.
I suggested the Nz in case the user was going to do anything further with the textbox result

Juett
Please can you explain the WHERE clause in your query. Are you trying to filter for null values or empty strings? It looks odd whatever you're doing.

 

[Juett]

Apologies, yes, Bath SN is a text field, and so is S1_SN.

The WHERE clause is used when running the query directly, you enter the value for Bath SN and it displays the corresponding records that match that value. It works perfectly.

 

[arnelgp]

there were no underscore on [BATH SN], AND [SI SN] on the last query you show, or just my eyes.
is it a Parameter query?

 

[isladogs]

Ah I see. A parameter value. In such cases I usually prompt the user with something like [Enter a value for Bath_SN] in the parameter [] brackets

 

[Juett]

Yes it is a parameter value. You enter the value manually when you run the query directly. I want the form button to run it and enter the parameter value based on the Bath SN field that been filled in already on the form, then copy the S1 SN field result.

 

[arnelgp]

you can create a Function inside the form

Public Function fnGetS1SN()
With CurrentDb.QueryDefs("Qry_AutoPop")
    .Parameters(0) = Me!Bath_SN
    fnGetS1SN= .OpenRecordset()(2)
End With
End Function

on your code (Click event) of the button:

Me.S1_SN = fnGetS1SN()

 

[Juett]

That worked perfectly - thanks very much!

 

[arnelgp]

your'e welcome!