Multi-Records in single Form - not Using Continous 1

[isaidi]

I have data that looks like this..

EquipmentID | Element Name | ELement Value
--------------------------------------
321 TEMP 25
321 Voltage 300V


Element Name is a name of a field on a form, and Element Value is its Value.

I want to map several of these records to Fields in a single Page for Data Entry. The only way i can do this is using a Subform as a Continous Form.

But i can't use Continous Forms becuase i have a specific Layout/Template that I must use for this form.


Ideas??
I am open to suggestions.

thanks

 

[stephenk1973]

Keep the template but make it very clear there are other records against the EquipmentID. Large navigaition buttons, maybe hide the displayed buttons if only one record is returned. Display record counts on the template, record (1 of 2) etc Send a message to the screem based on the record count. Can't think of anything else.

Stephen

 

[isaidi]

But this still doesn't show all the information for equipment# at the same time. The users must be able to see all of the information for that equipment at once, and be able to modified. This would have been easy in Continous Forms.

maybe the only way i can do it is using DLookUp(..)
i would bound the Form to a query generating the Table shown above, and DLookup() each Element Value and put it in the appropriate field.

I might make the Text Box Name on the form directly match the ElementName, and thus i can populate Element Value in a Loop.... Do think that is good ?

i will just have to figure out a way to save changes, as the TextBoxes won't be bound to a field.

 

[TheAceMan1]

how are ya isaidi . . .

Have a look here faq702-5860

See Ya! . . . . . .

 

[isaidi]

Thanks TheAceMan1,
but that doesn't not help me. I am not looking to requery/Synchronize a Subform. Infact if if did this, i would have 50 Subforms for my main form.

Like i said above, I have elements and their values associated with one Equipment# as follows..

Equip# | Element Name | Element Value
-===---------------------------------
321 Voltage 600V
321 Temp 90
321
........... ETC, there is about 40-50 records


I need to populate TextBoxes with ElementValues for viewing and dataEntry in one display according to a template/layout.

I can do this using Continues Subforms but it won't work for me, the fields are all over the Page. They Have to be layed out according to the template.

 

[Remou]

If you must have your table set up in this fashion, I think you must loop through the recordset (select * .. where EquipID = variable) to fill the unbound form, and similarly loop to update.

 

[isaidi]

I think you are Right Remou. There are several way to loop through.

I can narrow down the recordset to a single equipID. user selects the equipID he wants to view and opens the form for data/entry and view

i was thinking somthing like
(psuedo) (I still learning the how to use DAO )

Onform Load
rs=recordset.clone
for each control in me.AllControls do
     filter or find record where "Element Name" = control.name
      control.value = rs!value
end for

and for saving i would do the same thing except backwards.


Suggestions or Improvements ??

 

[isaidi]

Addition:
what would be the fastest code to do that step ?

filter or find record where "Element Name" = control.name

 

[Remou]

I think that the user enters the equipid on an unbound form (or selects from a combo). Then you get the recordset (typed, not tested):

Sub cboEquipID_AfterUpdate  
Dim rs as DAO.Recordset
Set rs= CurrentDB.OpenRecordset("Select * From tblEquip Where EquipID = " & Me.cboEquipID)
Do Until rs.EOF()
   ... fill in values
Loop
End Sub

 

[isaidi]

This is what worked for me. The Key was to ensure that the control names in the forms match the FIeldNames of your elements that you would like to fill. Then you can use a simple loop like shown below.

It is just rough code to demonstrate the idea.

Hope this will help someone..

Sub FillFormFields(EquipExbtID As Long)
    Dim rs As DAO.Recordset
    
    Set rs = CurrentDb.OpenRecordset( _
    "SELECT EquipExbtID, ElementName, ElementValue FROM tblElements INNER JOIN tblFormElements ON  ElementID=ElementID WHERE (((EquipExbtID)=" & EquipExhbtID & "));")
    
    
    rs.MoveFirst
    On Error GoTo PopulateError:
     Do Until rs.EOF()
       Forms("frmName").Form(rs!ElementName).Value = rs!ElementValue
       rs.MoveNext
     Loop
    rs.Close
    Set rs = Nothing
Exit Sub