Query + VB in form 1

[englundn]

Hi,
I have a problem which i can't figure out how to do it.
I have a table ie. :

steering_wheel wheels nose
boat true true false
dog false false true
bike false true false


What i want is to populate a listbox with the headings that are true. Meaning for dog, i just want to show the "nose" controls and so on. I know how to do it with single controls with dlookup function. But now i would like these values into a listbox and from there you can just choose the "nose" when "dog" is chosen in a combo box.

And eventually i want to open a form within a subform with the controls i need. This i already know how to do, but to populate the listbox is quite difficult.

 

[Remou]

Your table design does not appear to be normalised. I would have expected a table where it would be possible to pick all 'dog' controls.

[tt]tblThings
TID Descr
1 Dog
2 Boat

tblControls
CID Descr
101 Nose
999 Wheels

tblThingControls
TID CID
1 999
1 101[/tt]

 

[kjv1611]

I would think we'll need a little more info. From the sample data you have provided, what you want just doesn't seem to make sense to me. Of course, I may just be missing something.

--

"If to err is human, then I must be some kind of human!" -Me

 

[englundn]

It's intentionally not normalized. It's only controlling a maximum of 10 records, so i decided that i wont win anything in this situation. It's not good if i overnormalize a simple table either.

 

[englundn]

It's supposed to enable and disable certain controls depending on what kind of "tool" is under editing. For example for a "TV" i don't need to insert the number of wheels so i should disable the control in a Form so that user can't insert the number of wheels into the TV record.

 

[JoeAtWork]

It's not good if i overnormalize a simple table either

Overnormalize? What does that mean? Either it is or it isn't (i.e. either it's done correctly or it's not).

If it were normalized, as per Remou's example, this would be a simple SQL statement.

 

[englundn]

What does this kind of normalization help? I still get a table with some duplicate fields + i get 2 extra tables which isn't that much of a help. I don't have a problem of doing this i'm just wondering how this will help.

You can select all of the dog records because the dog is the primary key.

(id, steering_wheel, wheels, nose)

 

[englundn]

And then to my initial problem, if i make the normalized table. How do i update the Nose = True into the table "tblThingControls" as the normalization implicates. And alos remove that record when a check box is chosen to False.

Normalization in this case makes it so much more complicated compared to intentional unnormalized form which doesn't mean automatically that it's a bad design.

 

[Remou]

You will not have the difficulty of saying "True", you can say:

[tt]SELECT tblControls.CID, tblControls.Descr
FROM tblThingControls
INNER JOIN tblControls
ON tblThingControls.CID = tblControls.CID
WHERE tblThingControls.TID=1[/tt]

(From above, 1=Dog)

Normalization in this case makes it so much more complicated compared to intentional unnormalized form which doesn't mean automatically that it's a bad design.

This may be so, but if you are having difficulties, it cannot be that much easier. Perhaps the right way, in this case, is the easier way. You may wish to read:

http://www.r937.com/relational.html

 

[englundn]

I know the database normalization, not perfect but quite well. I've done the rest of my database into 3rd normal form, but in this case both of the options make it difficult.

When the database is normalized i know how to get the information out (as i wrote before), but what i don't know is how to update it the way i want it to be updated.

If i want to update one to many relationship from one form without using subforms or combo boxes.

http://koti.mbnet.fi/elvisp/visibility.png

This worked when my table wasn't normalized, but now i don't know how to solve the updatability.

 

[MajP]

If you do not decide to normalize (which would make this very simple) you could use a recordset to populate the control. Here is an example.

Private Sub Form_Current()
  Dim rst As DAO.Recordset
  Dim fldField As DAO.Field
  Set rst = CurrentDb.OpenRecordset("tblControls", dbOpenDynaset)
  Do While Not rst.EOF
    For Each fldField In rst.Fields
      If fldField.Value = True Then
        Combo2.AddItem rst.Fields("description") & ";" & fldField.Name
      End If
     Next fldField
    rst.MoveNext
  Loop
End Sub

This makes a combo box that looks like

Dog Nose
Bike Wheel
Boat Steering_Wheel
Boat Wheel

 

[englundn]

Ok, thank you. First that actually answered my question.

Next, if i use the normalization which i have a version of already. So how would i update the one to many relationship.

Working now with somekind of VB update recordset, any ideas for that?

 

[MajP]

assuming you had a table like

tblThingControls
TID CID
1 999
1 101

I would have a "Things" form with a "Controls" subform. The recordsource for the "Things" form is the "tblThings" table. The recordsource force the "Controls" subform is the "tblThingsControls" table. The main form is linked to the subform by TID. The CID in the subform is a combobox showing the Control name, but placing the CID into the table.

 

[englundn]

I have that solution for other types of usages. But for this specific form it would be good for the sake of simplicity for the user to immediately see what is disabled and what is not. So check boxes should be used.

 

[englundn]

I was thinking of this kind of solution to update the check boxes and similar to add a new record. But this is not a good solution either, because it's more memory consuming to have a this for every check box, than just a "update" function (already inside access) and a un-normalized table.
I would like to hear your opinions on how to update a normalized table with check boxes.

Private Sub Delete_Contact_Click()
Dim rs As ADODB.Recordset
Dim strSQL as string
On Error GoTo HandleError
Set rs = New ADODB.Recordset
strSQL = “SELECT * FROM tblContacts “ _
& “WHERE [ContactID] = “ _
& Me![txtContactID]
rs.Open strSQL, CurrentProject.Connection, _
adOpenDynamic, & adLockOptimistic
With rs
If not .EOF Then
‘Delete the record:
.Delete
End If
End With
ExitHere:
rs.Close
Set rs = Nothing
Exit Sub
HandleError:
MsgBox Err.Description
Resume ExitHere
End Sub

 

[MajP]

I use this idea quite often. I use normalized tables, but the form is not traditional. The trick is a temporary boolean field (blnTempSelected) that is used to control reading and writing to a different table. Here are my normal tables.

tblThings
autoThingID
strThingDescription

tblControls
autoControlID
strControlDescription
blnTempSelected (special field for writing to other table)

tblThings_Controls
intThingID
intControlID

Now I make a main form with a control source of tblThings. On the main form is a subform with a control source of tblControls. The only visible field on the main form is the "Thing" description. The subform is not linked to the main form. The subform is continous with "allow additions" and "allow deletions" set to false. Turn off the record selectors, scroll bars, and record navigation for aesthetics. The fields showing are the Control description and a check box for the field "blnTempSelected"

When you click or unclick on the subform it adds or deletes from tblThings_Controls:

Private Sub blnTempSelected_Click()
  Dim strSql As String
  Dim theThingID As String
  DoCmd.SetWarnings (False)
  theThingID = Me.Parent.autoThingID
  If Me.blnTempSelected Then
    strSql = "INSERT INTO tblThings_Controls (intThingID,intControlID) Values (" & theThingID & "," & Me.autoControlID & ")"
  Else
    strSql = "Delete * from tblThings_Controls where intThingID = " & theThingID & " and intControlID = " & Me.autoControlID
  End If
  DoCmd.RunSQL strSql
  DoCmd.SetWarnings (True)
End Sub

Now when you switch "Things" on the main form you need to clear out the temp checks and then write to the field blnTempSelected based on what is in tblThings_Controls.

Private Sub Form_Current()
  Dim rs As DAO.Recordset
  Dim rsUpdate As DAO.Recordset
  Dim strSql As String
   If Not Me.NewRecord Then
   DoCmd.SetWarnings (False)
   strSql = "UPDATE tblControls SET tblControls.blnTempSelected = False"
   DoCmd.RunSQL strSql
   Me.Refresh
   strSql = "Select * from tblThings_Controls where intThingID = " & Me.autoThingID
   Set rs = CurrentDb.OpenRecordset(strSql, dbOpenDynaset)
   Set rsUpdate = Me.subFrmAddCheck.Form.Recordset
   Do While Not rs.EOF
     rsUpdate.FindFirst "autoControlID = " & rs.Fields("intControlID")
     rsUpdate.Edit
     rsUpdate.Fields("blnTempSelected") = True
     rsUpdate.Update
     rs.MoveNext
   Loop
  End If
  docmd.setwarnings(true)
End Sub