Using Unbound Textbox to query table and use data on form

[Swiftraven]

Hi,

I am having a problem using an unbound text box to query the user for a number and then doing a query based on that input, then outputting the resulting data from the rest of the record back onto the same form (filling in data in corresponding boxes).

I set up a query that has its key criteria as the unbound box from the form (they are all unique values, so either only one or no records will exist). I have the rest of the form set to point to the query that is based on the user input. When I input the number and hit enter/tab, I can then open the query by hand and the data that I am looking for is there, but my forms data boxes are still blank. It seems that the forms data boxes are looking at the next (ie blank) record in the query and not the one with the information.

I am thinking its something easy that I am missing, but have yet to figure out what it is. Any help is appreciated.

Thanks for helping the newb.

Jason

 

[BoxHead]

Jason,

I don't quite understand what it is you're trying to do, but you should probably run

     Me.Form.Requery

on the AfterUpdate event for the textbox.

I'm curious as to why you want your form to only hold one record at a time. Using the ComboBox wizard and selecting "Find a record on my form based on the value I selected in my combo box." would let your users either type or scroll and click on the criteria for the record they're after.

Just some thoughts. John

Use what you have,
Learn what you can,
Create what you need.

 

[Swiftraven]

John,
What I have is a table that contains incident numbers. There are people assigned to each number that input resolution data and need to possibly change data that is already in the table.
I want them to be able to type in/have a drop box to input their incident number and have the related information come up in seperate boxes (ie. Technician Name, Part Number, etc), then they can input the resolution paragraph and change any incorrect data and then save it to the table.
I tried the combo box method you suggested, but I need for the data to fill in seperate text boxes, not one long drop down box. Is there any way to key on the incident number itself and have the rest fill in? Like I mentioned in my previous post, I got the query to gather the data I need, I just couldnt get that data to apprear in my form, it always grabbed the next rows (the new record, which is always blank) data.


Thanks for the help
Jason

 

[MangroBongacello]

If you have all the data in your combo box, use the column(x) property in AfterUpdate to fill in the text boxes. You can hide the columns you don't want to display by setting column widths property appropriately.

Sub Combo_AfterUpdate()
Text1=Combo.Column(x)
Text2=Combo.Column(x)
...
End Sub

x=sequential number of the column in combo box. Note that columns start counting from 0, so the 1st has index 0, the 2nd 1...

Hope this is what you're after.
Mangro

 

[BoxHead]

Jason,

It really seems that what you're after could be achieved very easily.

Would you be willing to try something (about ten minutes)?

Go to the Forms tab on the database window. Select NEW. Check 'AutoForm Columnar' and select the name of your table in the dropdown. Hit OK.

A new form based on the table will be generated. Go to the design view and drag the detail section a little wider so you have some room to work.

From your tool menu, make sure the wizard's wand is higlighted and select the ComboBox tool. Place it on the detail of your form. When the wizard opens, tick the third radio button that says, "Find a record on my form based on the value I selected in my Combo box." Click NEXT.

From the 'Available Fields' list, select the IncidentNumber field and select NEXT and then, FINISH.

Switch back to form view and from the combo box, select an incident number. All of the other fields, Technician, PartNumber, Resolution, should update to match the IncidentNumber.

From your posts, this seems to be the functionality you want. Or, am I missing something?

If this worked, compare the layout of the form you designed to the Form Wizard's and see what you can 'borrow' from it before you delete it.


HTH
John

Use what you have,
Learn what you can,
Create what you need.

 

[Swiftraven]

Thanks for the help Mangro and John.

John, I did what you said and that is almost exactly what I am trying to do. I tried applying the same process to my pre-existing form outline but get an error saying that there is no current record. When the form is brought up, I would like it to be blank until the user puts in the incident number, unlike the autoform that you had me create which always brings up data. When I used the same combo box on the form I make, it errors by saying there is no current record for the bookmark.

Sub Combo32_AfterUpdate()
' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[IncidentId] = " & Me![Combo32]
Me.Bookmark = Me.RecordsetClone
End Sub

I get a runtime error 3021, no current record on the Me.Bookmark = Me.RecordsetClone line. Is there an easy way to set the current record to a blank record or something? I have looked it up in my book and online help, but haven't gotten anywhere yet.

Thanks alot for your help

Jason

 

[BoxHead]

Jason,

Two questions:

Did you try the Me.Requery on the textbox AfterUpdate?

What records, if any, do you see on your form when you first open it?





John

Use what you have,
Learn what you can,
Create what you need.

 

[Swiftraven]

John,
No records are there when its opened. I would like it to be blank until the person selects the incident number, then have it fill in the text boxes with the appropriate data from the table.

I can't figure out how to "initialize" the Me.Bookmark to be, say a new or blank record, then have it set to the Bookmark pointed to by the selected Incident or just have it initialize to the record the user selects.

Where exactly should I put the Me.Requery? I placed it in the Combo AfterUpdate sub before the error spot but it's still a no go.

I already have a save record and clear record buttons, I would like to add a new record button to let them add new incidents also, but that will have to wait until this matter is resolved.

Thanks again for the help John

Jason