SQL Query Loads Value, but won't save

[SpankYou]

Hey,

Basically when a user enters a WorkRequestNumber in one text box this should automatically load the SalesOrderNumber into another textbox. As of yet I could not get this working. If anyone knows how to do this I'd be very grateful.

As an alternative I have created An SQL Query.
When the WorkRequestNumber textbox has been updated e.g the number "1234569" is entered. Using the AfterUpdate Event of the textbox, it then places the following SQL into the RowSource of a Listbox. (I used a listbox as I could not get the controlsource of a textbox to work)

"SELECT TblWorkRequest.SalesOrderNo FROM TblWorkRequest WHERE (((TblWorkRequest.WRNumber)= [forms]![FrmTimeSheetEntry]![WRNumber]));"

This works and places the value into the listbox, but when the record is added this value is not saved. I have found out that to actually get the value to add to the record, the listbox has to be clicked.

So my question is really 1 of 2 things,

1) is there a way to call up a field in textboxB, where the value of textboxA is equal to a records primary field.
e.g A user enters 1234569 into TextBoxA then some code searches for that field (which is a Primary Key)...

PK = 1234569, Field1 = "SON1"

If it finds it as above then it puts the Value of Field1 into TextboxB.

Or Question 2

2)Using the listbox method I have already coded is there a way to automatically store the value of the list box into the record? as apposed to having to maually click on the listbox to get it to actually add it.

Thanks in advance for any help, and please ask for clarification if unsure,

Cheers

Sam


"You couldn't fool your mother on the foolingest day of your life if you had an electrified fooling machine." - Homer

 

[SkipVought]

Hi,

I don't see what your problem is.

1. User enters into textbox1

2. Some event (???) fires the query with the text from textbox1 in the where clause

3. The SalesOrderNo is extracted from the resultset and assigned to textbox2.text.

Plain vanella!

???

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[SpankYou]

Oh and SkipVought, yeah that was the problem, but I wasn't sure on how to get the SalesOrderNo value into a parameter and subsequently into a textbox, but it is all sorted now....

On Error GoTo ErrorHandler
Dim SQL As String
Dim SQL1 As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sMyVar1 As String, sMyVar2 As String

SQL = "SELECT * FROM TblWorkRequest WHERE (((TblWorkRequest.WRNumber) = '" & TxtWRNumber.Text & "'));"

Set db = CurrentDb
Set rs = db.OpenRecordset(SQL) '(SQL)
rs.MoveFirst
Do While Not rs.EOF
sMyVar1 = rs!WRNumber
sMyVar2 = rs!SalesOrderNo
rs.MoveNext
Loop

TxtSalesOrderNo.SetFocus
TxtSalesOrderNo.Text = sMyVar2

ExitSub:

Exit Sub
ErrorHandler:

If Err.Number = 2115 Then
Resume ExitSub
End If


Cheers


Sam

"You couldn't fool your mother on the foolingest day of your life if you had an electrified fooling machine." - Homer