MS Access 2000/2003 Text Box Question

[MichaelF81]

I am working on a form in Access 2003 (Access 2000 format), and i have this form built that all pulls from 1 table.

Inside of this form I need to have a textbox fill with data that does not come from tha recordset, but updated each time for each recordset.

Essentially, I need to make the text box read the txt box named "name", run a query on all products linked to "name" and fill the box with the total amount of values.

Any ideas? Does this need to be done in VB, and how?

Thanks in nadvance

 

[PHV]

Have a look at the DSum function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ChanceD2]

Michael,

Yes, I would do this in VBA. Give some more information and you'll get a better, more useful response. I/we need table name, form name, objects on form that are to be queried, a SQL string based on the form that does what you are trying to do would be great.

Let me know if I am unclear or you are lost and I'll explain more.

HTH

C-D2

 

[MichaelF81]

Ok, here it goes.

The main data from the form is stored in tbl-RoomData, but the data that I need the records counted (Not a sum, but actual recrod count) is in tbl-EquipmentData.

The main form is called frm-Room Data Entry, and the equipment form (it is opened in a new window calling the primary key) is called frm-Equipment, with a subform (which actually holds the data (the equip form just has the pk on it, and the sub-form has all the records)) called sfrm-Equip.

The text field on the main form (for the PK) is 'Space Code' which the equipment form pulls, and then uses for the equipment data.

The text field where I want the record count to go is 'txtEquipCount'.

Please let me know if you need anything else.

Thanks,

Michael

 

[TheAceMan1]

How are ya MichaelF81 . . .

My first indication is [blue]you need a custom query field.[/blue] This field would be based on a function that receives the current [blue]Name[/blue] in the current record in the query and return the proper count thereof. In [blue]Query Design View[/blue] it would look something like:

[blue]FullCnt:NameCnt([Name]).

In the query [blue]Name[/blue] for each record gets passed to the function [blue]FullCnt[/blue] and the function returns that asked for:
[code][blue]Function NameCnt(Name as String) as Long
   [green]'Code[/green]
   NameCnt = [green]'Answer in code[/green]
End Function[/blue]

The function should reside in a module in the modules window . . .

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

 

[Godofhell]

You could create a recordset in where you retrieve the records from the table that match the value on your text box, then count the recordset and add the value to the txtEquimCount.


myRecordset = Connection.Execute("Select IDField from tbl-EquipmentData Where Fieldtolookfor = mysearchcriteria.value")

txtEquimCount.value = cStr(myRecordset.RecordCount)

Note that the above code has not been tested and the connection is not defined. If someone wants to create the actual code, please do so, I am simply showing a possible solution.