Changing value of text box populated from query

[Marie]

Using Access 2000.

I have about five sources for ordering materials. To
actually order the materials, my report (from a query)
finds all entries with a source of CRI, for example.

In my input form, I enter the resource number (unique) and
the source populates from a products table.

The problem is sometimes I want to manually enter a
different value in the source text box, e.g., if the
source doesn't have the material, I would on a rare
occassion order from a different source. But if I type
something different in the source textbox, the product
table source field is changed--which it shouldn't be.

How can I have the source field automatically populated,
but then be able to change it as needed.

Really appreciate your help!

Marie

 

[Adrian Jansen]

You need to use an unbound control for the source text box, and populate it
only when the value in the resource number ID changes. Then you can type a
manual value into it as well.

For instance if you use the Before Update event of the resource number box:

Me.txtSource = Me.Recordsetclone!Source

Adjust the names to suit your fields and controls.

--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control

 

[Guest]

Adrian,

I am a beginner with coding. So would that be:

Me.txtSource = Me.Recordsetclone!qrySamplesInputForm
(where qrySamplesInputForm is the query that the Input
Form is based on) or do I put something in the place
of "Recordsetclone" also?

With this approach, does the value in txtSource get stored
back to the tblSamples? Will I have to put a fldSource in
my tblSamples? If so, how does the txtSource value get
stored in the fldSource in the tblSamples? I am not sure
how the query for my report will find the criteria = CRI,
for example.

Hope this makes sense. I think I am in over my head. Maybe
you could suggest an article or book that discusses this.

Thanks so much.
Marie

 

[Adrian Jansen]

Access has a steep learning curve, particularly when you get into VBA
programming as well. So it takes some getting the hang of where everything
fits.

More comments inline:
--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control

Adrian,

I am a beginner with coding. So would that be:

Me.txtSource = Me.Recordsetclone!qrySamplesInputForm
(where qrySamplesInputForm is the query that the Input
Form is based on) or do I put something in the place
of "Recordsetclone" also?

No. The RecordsetClone is the data source for any bound controls on the
form, and already is the recordset from your qrySamplesInputForm.
What you need to refer to is the particular field from that query which
returns the Source name. It would help if you posted the names of your
fields, and the names of the controls on your form, so we have something
concrete to refer to.

With this approach, does the value in txtSource get stored
back to the tblSamples? Will I have to put a fldSource in
my tblSamples? If so, how does the txtSource value get
stored in the fldSource in the tblSamples? I am not sure
how the query for my report will find the criteria = CRI,
for example.

The value in txtSource will only get stored back in the table ( via the
forms RecordsetClone ) when you do something in the txtSource Before Update
event, like putting

me.RecordsetClone!YourSampleName = me.txtSource

the 'me' keyword refers to the current form, and YourSampleName is the name
of the field in the query holding the data. Because the forms recordset is
already pointing to the current CID, that record is the one that gets
updated with the value in txtSource.

Hope this makes it a bit clearer.

Hope this makes sense. I think I am in over my head. Maybe
you could suggest an article or book that discusses this.

There are lots of books, the best IMHO is the Access 2002 Developers
Handbook by Litwin & Getz. But there are plenty of others showing examples
of VBA coding, and even the Access Help file is reasonable, except that its
hard to find examples in it. Look also through the posts on this newsgroup,
and also in formscoding.