auto update fields from textbox input

[cliff7376]

I am having a Braincramp. I have a subform where i want a user to type in a job number. Once the jobnumber is typed in I want to go out to a table in my database and get the POID, Serial number, and partnumber and auto input them in my subform automatically. I think I have done this before but I am totally spacing on how I did it. Can anyone help me.

Thanks
Sean

 

[databasedonr]

use the input textbox to drive your query by placing the reference in your criteria field on the qbe grid.

For example, you would place [Forms]![formname]![textbox] in the criteria field.

This will run your query based on whatever is entered in the [textbox] field on your form.

Then, base your subform on the query....

I hope this makes sense --

signed,

nearly brain-dead on a Friday p.m.

 

[jaydwest]

On the AfterUpdate event of your JobNumber field, use a parameter query of generate the SQL to get this data and display it in the other fields.

Query might look like

SELECT POID, Serial number, partnumber FROM <Table Name> WHERE (<Table Name>.JobNumber = '" & JobNumber & "'); "

Assumes JobNumber is String. This is the straight SQL version, use the Query Design Window to generate the Parameter Query.

If a recordset with a record is returned, then assign the values to the other fields

[POID] = rst!POID
[Serial Number] = rst![Serial Number]
[partnumber] = rst!PartNumber

You could also do this with a combo box on the Job Number field. This is easier from a coding standpoint, but might be slower if the Table has a large number of records. Is there any reason not to use a combo box?

If you have any questions, let me know.

 

[cliff7376]

I like to use sql statements in my code. It seems more like what i want. Now i just have to get that coding part set. All i do is dim rst as a recordset right?

Thank you

Both answers were very helpful. I am just so braindead today.

 

[jaydwest]

here's the code

dim rst as DAO.Recordset
dim mySQL as string

mySQL = "QUERY SQL"
set rst = dbs.OpenRecordset(mySQL)
IF not rst.EOF then
<Assign Fields Here>
Else
<Clear Fields Here>
End If

rst.close: Set rst = nothing

You can clear the fields by setting the Me![ControlName] = Null. Also there may be a table field with a Null value so you should use NZ as shown below in the Assign Fields Here section.

Me![Serial Number] = NZ(rst![Serial Number], Null)

If you have any questions, let me know.

 

[cliff7376]

I have gotten the sql statement to run without errors. The problem I am having now has to do with the way I am Assigning my fields the returned values. ( Error: The value you entered isn't valid for this field.)Could you take a look at it and tell me what I might be doing wrong. Actually the Sql statement might not be working because the only way i can get the code to try and assign it values is by putting it in the EOF else stament. Is there a way i can see the values the sql statement is picking up in debug mode? Both data types are text

Thanks
Sean

Private Sub Jobnum_AfterUpdate()
Dim dbs As Database
Dim rst As DAO.Recordset
Dim SQL As String
Set dbs = CurrentDb

mySQL = "SELECT REC_TrackNumber, REC_PartNumber, REC_CusPurchaseorder, REC_SerialNumber FROM REC WHERE REC_TrackNumber='&[Forms]![QOD_subform]![Jobnum]&'"






Set rst = dbs.OpenRecordset(mySQL)
If Not rst.EOF Then

'<Assign Fields Here>
Else
[Sernum] = rst![REC_SerialNumber]
'<Clear Fields Here>
End If

rst.Close: Set rst = Nothing

 

[jaydwest]

You got the assignment backwards

If Not rst.EOF Then
[Sernum] = rst![REC_SerialNumber]
Else
[Serum] = null
End If

 

[cliff7376]

I know I have them messed up. What i am trying to say is that i do not think that i am getting the data from the sql query. after the code passes this juncture

set rst = dbs.OpenRecordset(mySQL)

rst is showing the sql statemnt as what is in it. How can I find out if I am getting values out of the database. I know the query works becouse i pasted it into the sql view and got the results i wanted.

Any ideas

 

[Pat Hartman]

You are going about this the long way. All you need is to base the form on a query that joins the two tables. That way columns from both tables will be available from the field list.

If your form is presently bound to a table, make the query that selects the columns you need and then in the RecordSource property, replace the table name with the query name. Your form will still work as before as long as you haven't changed any field names. Then just add the new control with the column from the second table.

 

[cliff7376]

Can I send you my database so you can help me out please. I have no idea why this is so hard for me but i am really confused. I tried using the query like you said but i can't get that to work either.

 

[cliff7376]

Can I send you my database so you can help me out please. I have no idea why this is so hard for me but i am really confused. I tried using the query like you said but i can't get that to work either.


thanks
sean

 

[cliff7376]

nevermind. I used dlookup. It worked exactly the way i wanted it to.

Thanks Again
Sean

 

[Pat Hartman]

You need to learn how to use queries with joins. DLookup()s are very ineffecient.