When to use unbound

[Cereldine]

Ive been looking through the posts to see the merits of using bound and unbound forms. I have the following scenario and think that a unbound form would be best solution, what does anyone else think?

I have carried out normalisation of the system and have ended up with two identical tables called Broad themes, one of these tables relates to a documents theme, one to a organisations theme. Organisations and documents are both directly related in the table structure making it impossible to just use one Broad themes table (Circular).

I want to give the user an option form to enter the relevant criteria into unbound text boxes then select using an option group which table to enter information into or option to insert into both tables.

Is this where a unbound form would be most appropriate?

 

[KenHigg]

Hum... Why can't you combine the tables and have a fld that indicates which type theme it refers to?

 

[Cereldine]

mainly because it is quite a complex table structure and the fact a theme may relate to a document and a organisation. Thanks tho i have considered this already, the structure just makes it near immpossible!

 

[RuralGuy]

You may be correct but using phrases like:

...the structure just makes it near immpossible!

makes me shake my head and shudder. Ken knows what he is talking about.

 

[Cereldine]

theres no need to be scared! The structure was already created, and the database contains data already, if i start making fundamental structure changes some of the info will not be available. only got a couple of days to complete the work so this seemed easiest way

 

[RuralGuy]

Phrases like this:

...ended up with two identical tables...

should set off alarms. That being said, the answer to your original question of:

Is this where a unbound form would be most appropriate?

is probably. You *will* however have to do all of the data saving work yourself.

 

[Cereldine]

Code i have used

Anyways, here is the code i have used, it may need some ironing out

Option Compare Database



Private Sub cmdInsert_Click()
On Error GoTo CustomErr
Dim strTheme As String
Dim message As String
txtTheme.SetFocus
strTheme = Me.txtTheme.Text

If Me.txtTheme.Text = "" Then
MsgBox "Enter a value "
Else

If optInsert.Value = Null Then
MsgBox "Choose a Table"
Else
Select Case optInsert

Case "1":
message = "Enter " & strTheme & " Into themeOrganisation table"
If MsgBox(message, vbYesNo) = vbYes Then
Cancel = False
SQL = "INSERT INTO themeorganisation([Broad Theme]) VALUES('" & strTheme & "') "
DoCmd.RunSQL SQL
Cancel = True
End If

Case "2":
message = "Enter " & strTheme & " Into themedocuments table"
If MsgBox(message, vbYesNo) = vbYes Then
Cancel = False
SQL = "INSERT INTO themedocument([Broad Theme]) VALUES('" & strTheme & "') "
DoCmd.RunSQL SQL
Else
Cancel = True
End If

Case "3":
message = "Enter " & strTheme & " Into themeOrganisation table AND themeDocuments table?"
If MsgBox(message, vbYesNo) = vbYes Then
Cancel = False
SQL = "INSERT INTO themeorganisation([Broad Theme]) VALUES('" & strTheme & "') "
DoCmd.RunSQL SQL

SQL = "INSERT INTO themedocument([Broad Theme]) VALUES('" & strTheme & "') "
DoCmd.RunSQL SQL
Else
Cancel = True
End If
End Select
End If
End If
Exit Sub
CustomErr:
MsgBox " Error occurred, " & err.Description
End Sub

 

[RuralGuy]

You need:
Option Compare Database
Option Explicit
at the top of your code module. I would probably use an OptionGroup with three choices and a save button. Another note: If you use the default txtTheme.Value property you do *not* have to set the focus to the control first.

 

[KenHigg]

Maybe you can fix it right in V2.0 (?)