Form Header question

[Jaz]

I have a form that has a FORM header and footer. In the form header there
are 2 fields, 'part number' and description. When I open up the form I have
a query that asks for the part number and then it pulls up information for
that part number.

In the details section, it shows information about that part number for each
instance it shows up in the database.

Is it possible to be able to directly type in the part number field and have
the information populate instead of have to reopen the form to have the
query prompt for another part number?

I don't want to use a button to enter a new part number, I just want to be
able to enter the part number in the field and have it automatically pull
the information up.

Any ideas?
Thanks
Jasper

 

[Dirk Goldgar]

I have a form that has a FORM header and footer. In the form header
there are 2 fields, 'part number' and description. When I open up
the form I have a query that asks for the part number and then it
pulls up information for that part number.

In the details section, it shows information about that part number
for each instance it shows up in the database.

Is it possible to be able to directly type in the part number field
and have the information populate instead of have to reopen the form
to have the query prompt for another part number?

I don't want to use a button to enter a new part number, I just want
to be able to enter the part number in the field and have it
automatically pull the information up.

Any ideas?
Thanks
Jasper

It depends on how the form and query are set up. If the part number
text box in the form's header is bound to a field in the form's
recordsource, you don't want to type in it because you'd be changing the
part number of an existing record. But if it's unbound, and the query
has a reference to that text box as a criterion, then you could type a
new part number in the text box, and have code in the control's
AfterUpdate event requery the form.

What's the SQL of the form's recordsource? What's the ControlSource of
the text box?

 

[Jaz]

What did you mean when you asked "What's the SQL of the form's
recordsource?" I'm not sure what that means.

The controlsource of the textbox is linked to a field in the query. When I
removed that link and created a macro that used the requery option. I did
not put a control source.

When I enter in a new number in the part field, the query form pops up also
and ask for the part number again. How can I not make that pop up and just
have the form populate after I enter in the part the first time?

Also, how would I have the part number field clear itself when I click in
it. I don't want to have to highlight everything in that field to type over
it.

Thanks,
Jasper

 

[Dirk Goldgar]

What did you mean when you asked "What's the SQL of the form's
recordsource?" I'm not sure what that means.

From your description, the form's RecorSource property (which you can
find on the Data tab of the form's property sheet in design view) is
either a SQL statement or the name of a stored query. I was curious as
to the exact SQL of that query.

The controlsource of the textbox is linked to a field in the query.
When I removed that link and created a macro that used the requery
option. I did not put a control source.

When I enter in a new number in the part field, the query form pops
up also and ask for the part number again. How can I not make that
pop up and just have the form populate after I enter in the part the
first time?

What I would probably do, if I understand rightly what you're trying
for, is have the form's query refer to the textbox for its criteria,
rather than using your current parameter query. So where you might
currently have something like this:

SELECT * FROM YourTable
WHERE PartNumber = [Enter the part number:];

You might change it to


SELECT * FROM YourTable
WHERE PartNumber = [Forms]![YourFormName][YourTextBox];

The text box on the form would be unbound -- that is, its ControlSource
property would be blank. You would have a very simple event procedure
for the text box's AfterUpdate procedure:

Private Sub YourTextBox_AfterUpdate()

Me.Requery

End Sub

With this arrangement, when the form first opens it will show no
records. You then type a part number in the text box and press Enter or
Tab, and voilá! The matching records appear.

Also, how would I have the part number field clear itself when I
click in it. I don't want to have to highlight everything in that
field to type over it.

Clicking tends to override whatever your setting you had for selecting
the field when it gets the focus. If you want to clear it whenever you
enter it, you could use an event procedure like this for its Enter
event:

Private Sub YourTextBox_Enter()

Me!YourTextBox = Null

End Sub

 

[Jaz]

Perfect!

Thanks!
Jasper

What did you mean when you asked "What's the SQL of the form's
recordsource?" I'm not sure what that means.

From your description, the form's RecorSource property (which you can
find on the Data tab of the form's property sheet in design view) is
either a SQL statement or the name of a stored query. I was curious as
to the exact SQL of that query.

The controlsource of the textbox is linked to a field in the query.
When I removed that link and created a macro that used the requery
option. I did not put a control source.

When I enter in a new number in the part field, the query form pops
up also and ask for the part number again. How can I not make that
pop up and just have the form populate after I enter in the part the
first time?

What I would probably do, if I understand rightly what you're trying
for, is have the form's query refer to the textbox for its criteria,
rather than using your current parameter query. So where you might
currently have something like this:

SELECT * FROM YourTable
WHERE PartNumber = [Enter the part number:];

You might change it to


SELECT * FROM YourTable
WHERE PartNumber = [Forms]![YourFormName][YourTextBox];

The text box on the form would be unbound -- that is, its ControlSource
property would be blank. You would have a very simple event procedure
for the text box's AfterUpdate procedure:

Private Sub YourTextBox_AfterUpdate()

Me.Requery

End Sub

With this arrangement, when the form first opens it will show no
records. You then type a part number in the text box and press Enter or
Tab, and voilá! The matching records appear.

Also, how would I have the part number field clear itself when I
click in it. I don't want to have to highlight everything in that
field to type over it.

Clicking tends to override whatever your setting you had for selecting
the field when it gets the focus. If you want to clear it whenever you
enter it, you could use an event procedure like this for its Enter
event:

Private Sub YourTextBox_Enter()

Me!YourTextBox = Null

End Sub


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)