Record Source

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form that is setup like

Collection _________

Budget Actual
Group 1 _______ ______ blanks to enter info
Group 2 _______ ______
Group 3 _______ ______ .....

I want the information to be put in a table that is setup like

Collection Group Budget Actual
Col 1 Grp 1 10 20
Col 1 Grp 2 5 10
Col 1 Grp 3 3 8
Col 2 Grp 1 15 17 ....

How do I make the record source for the blanks in the form?

(I know this isn't right but something like CollectionTable.Group.[Budget]
and CollectionTable.Group.[Actual]
 
Instead of having two columns for "amount" ([Budget], [Actual]), use a
single column to hold [Amount], and a single column to hold something like
[Type]. In the [Type] column, indicate whether the amount is "budget" or
"actual". This is very much like what you've already done with [Collection]
and [Group].

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
If the form really has to be laid out this way and fast data entry is
important, I'd use an unbound form with the seven (or however many) textboxes
and a Save button. Code behind the button would execute three (or more: one
per record) single-record append queries to move the data into the table. Air
code:

Set dbD = CurrentDB()
dbD.Execute "INSERT INTO MyTable (Collection, Group, Budget, Actual) " _
& VALUES (" _
& "'Col " & Me.txtCollection.Value & "', 'Grp 1', " _
& Me.txtBudget1.Value & ", " & Me.txtActual1.Value & ");", dbFailOnError
dbD.Execute "INSERT INTO MyTable (Collection, Group, Budget, Actual) " _
& VALUES (" _
& "'Col " & Me.txtCollection.Value & "', 'Grp 2', " _
& Me.txtBudget2.Value & ", " & Me.txtActual2.Value & ");", dbFailOnError
....
'then clear the textboxes and insert any default values ready for entry of
the next group of records.


Or for simplicity, use a bound form in continuous view. Put a textbox
txtCollection bound to the Collection field in the form header, with code in
its AfterUpdate event
Me.txtCollection.DefaultValue = Me.txtCollection.Value
so you only have to type a new value when starting a new collection.

Use a combo box (limit to list turned on) bound to the Group field, so the
user can only (and easily) select the groups. Code in this control's
AfterUpdate event can set its default value to the next required, e.g.
With Me.txtGroup
Select Case .Value
Case 1
DefaultValue = 2
Case 2
DefaultValue = 3
Case 3
DefaultValue = 1
End Select
End With






awach said:
I have a form that is setup like

Collection _________

Budget Actual
Group 1 _______ ______ blanks to enter info
Group 2 _______ ______
Group 3 _______ ______ .....

I want the information to be put in a table that is setup like

Collection Group Budget Actual
Col 1 Grp 1 10 20
Col 1 Grp 2 5 10
Col 1 Grp 3 3 8
Col 2 Grp 1 15 17 ....

How do I make the record source for the blanks in the form?

(I know this isn't right but something like CollectionTable.Group.[Budget]
and CollectionTable.Group.[Actual]
Click to expand...
 
I'm not really familar or good as using the codes. Is there a way to do this
using the controls/design view?


John Nurick said:
If the form really has to be laid out this way and fast data entry is
important, I'd use an unbound form with the seven (or however many) textboxes
and a Save button. Code behind the button would execute three (or more: one
per record) single-record append queries to move the data into the table. Air
code:

Set dbD = CurrentDB()
dbD.Execute "INSERT INTO MyTable (Collection, Group, Budget, Actual) " _
& VALUES (" _
& "'Col " & Me.txtCollection.Value & "', 'Grp 1', " _
& Me.txtBudget1.Value & ", " & Me.txtActual1.Value & ");", dbFailOnError
dbD.Execute "INSERT INTO MyTable (Collection, Group, Budget, Actual) " _
& VALUES (" _
& "'Col " & Me.txtCollection.Value & "', 'Grp 2', " _
& Me.txtBudget2.Value & ", " & Me.txtActual2.Value & ");", dbFailOnError
...
'then clear the textboxes and insert any default values ready for entry of
the next group of records.


Or for simplicity, use a bound form in continuous view. Put a textbox
txtCollection bound to the Collection field in the form header, with code in
its AfterUpdate event
Me.txtCollection.DefaultValue = Me.txtCollection.Value
so you only have to type a new value when starting a new collection.

Use a combo box (limit to list turned on) bound to the Group field, so the
user can only (and easily) select the groups. Code in this control's
AfterUpdate event can set its default value to the next required, e.g.
With Me.txtGroup
Select Case .Value
Case 1
DefaultValue = 2
Case 2
DefaultValue = 3
Case 3
DefaultValue = 1
End Select
End With






awach said:
I have a form that is setup like

Collection _________

Budget Actual
Group 1 _______ ______ blanks to enter info
Group 2 _______ ______
Group 3 _______ ______ .....

I want the information to be put in a table that is setup like

Collection Group Budget Actual
Col 1 Grp 1 10 20
Col 1 Grp 2 5 10
Col 1 Grp 3 3 8
Col 2 Grp 1 15 17 ....

How do I make the record source for the blanks in the form?

(I know this isn't right but something like CollectionTable.Group.[Budget]
and CollectionTable.Group.[Actual]
Click to expand...
Click to expand...
 
Could I use a query somehow to make the change in format and then make the
table from the query? How would I do that?
 
Not if you want the form and the table to be as you describe.

I'm not really familar or good as using the codes. Is there a way to do this
using the controls/design view?


John Nurick said:
If the form really has to be laid out this way and fast data entry is
important, I'd use an unbound form with the seven (or however many) textboxes
and a Save button. Code behind the button would execute three (or more: one
per record) single-record append queries to move the data into the table. Air
code:

Set dbD = CurrentDB()
dbD.Execute "INSERT INTO MyTable (Collection, Group, Budget, Actual) " _
& VALUES (" _
& "'Col " & Me.txtCollection.Value & "', 'Grp 1', " _
& Me.txtBudget1.Value & ", " & Me.txtActual1.Value & ");", dbFailOnError
dbD.Execute "INSERT INTO MyTable (Collection, Group, Budget, Actual) " _
& VALUES (" _
& "'Col " & Me.txtCollection.Value & "', 'Grp 2', " _
& Me.txtBudget2.Value & ", " & Me.txtActual2.Value & ");", dbFailOnError
...
'then clear the textboxes and insert any default values ready for entry of
the next group of records.


Or for simplicity, use a bound form in continuous view. Put a textbox
txtCollection bound to the Collection field in the form header, with code in
its AfterUpdate event
Me.txtCollection.DefaultValue = Me.txtCollection.Value
so you only have to type a new value when starting a new collection.

Use a combo box (limit to list turned on) bound to the Group field, so the
user can only (and easily) select the groups. Code in this control's
AfterUpdate event can set its default value to the next required, e.g.
With Me.txtGroup
Select Case .Value
Case 1
DefaultValue = 2
Case 2
DefaultValue = 3
Case 3
DefaultValue = 1
End Select
End With






awach said:
I have a form that is setup like

Collection _________

Budget Actual
Group 1 _______ ______ blanks to enter info
Group 2 _______ ______
Group 3 _______ ______ .....

I want the information to be put in a table that is setup like

Collection Group Budget Actual
Col 1 Grp 1 10 20
Col 1 Grp 2 5 10
Col 1 Grp 3 3 8
Col 2 Grp 1 15 17 ....

How do I make the record source for the blanks in the form?

(I know this isn't right but something like CollectionTable.Group.[Budget]
and CollectionTable.Group.[Actual]
Click to expand...
Click to expand...
Click to expand...
 
Back
Top