Place data from Form into Table

[Matt Reed]

I have a form that pulls that displays the montly totals from three seperate
reports.
A fourth text box adds those three and shows the Sum.
Another text box shows the date.
Now I want to add this five items to a table.
The table has six columns Id, CheckDate sum1, sum2, sum3 and TotSum.
By clicking on the Record Button, it should check to make sure there is not
a record for this date already in the table and if not enter a new record
with the data from these 5 text boxes.

How can I do this - or am I going about this all wrong?

Matt

 

[Don Wilson]

I wouldn't say your going about it wrong. There are just
other things you can do.

I suggest you research VBA, and the DLookup Function.

When you are inputting the actual data into the fields.

You can add some code the "after Update" event of the
field you are inputting.

Here is an example.

Dim Sum1 As Currency
Dim Sum2 As Currency
Dim Sum3 As Currency
Dim SumTtl As Currency

Sum1 = Forms!frmMain!sfrmName!Sum1
Sum2 = Forms!frmMain!sfrmName!Sum2
Sum3 = Forms!frmMain!sfrmName!Sum3
SumTtl = Sum1 + Sum2 + Sum3

I guess what i am trying to tell you is that the values
need to be stored to do what you want, and the above code
is an example of that.

I hope that helps.

 

[Matt Reed]

Thanks for the input.
Not sure I am caught what you are trying to say. Sounds like how to fill
the individual textboxes on the form. I have already got the form to show
the right data in each textbox, including the textbox that totals the other
textboxes.

What I am trying to do now is put a button on the form, so that when that
button is pressed a new record is placed in the table Payments. Each field
on the form will be a field in the table.

I can get each field to write in a seperate record with a series of
statments like:
'DoCmd.RunSQL "INSERT INTO Payments (PaymentAmount) VALUES ('" &
Me.Sales1_Total.Value & "')"

But I have not figured out how to get multiple columns of one record to
update in one INSERT statement.

Tried various versions of this:
DoCmd.RunSQL "INSERT INTO Payments VALUES (Me.ReportMonth.Value,
Me.ReportYear.value, Me.Sales1Total.Value, Me.Sales2Total.Value )"

All the different tries either gave me syntax errors or asked for the value
of each item.

Hope that explains where I am a little better.

Thanks for responding so quickly.

Matt



I wouldn't say your going about it wrong. There are just
other things you can do.

I suggest you research VBA, and the DLookup Function.

When you are inputting the actual data into the fields.

You can add some code the "after Update" event of the
field you are inputting.

Here is an example.

Dim Sum1 As Currency
Dim Sum2 As Currency
Dim Sum3 As Currency
Dim SumTtl As Currency

Sum1 = Forms!frmMain!sfrmName!Sum1
Sum2 = Forms!frmMain!sfrmName!Sum2
Sum3 = Forms!frmMain!sfrmName!Sum3
SumTtl = Sum1 + Sum2 + Sum3

I guess what i am trying to tell you is that the values
need to be stored to do what you want, and the above code
is an example of that.

I hope that helps.

 

[Willy Wichtig]

Hi Matt,

first, I believe you can drop the ".Value", but not 100% certain
try this.... (and watch for line wrapping) *** untested ***

Dim sSql as String

sSql = "INSERT INTO Payments (CheckDate, sum1, sum2, sum3, TotSum) " & _
"Values (" & Format(Me.YourDateField, "\#mm\/dd\/yyyy\#") & ", " &
Me.ReportMonth & ", " & _
"" & Me.ReportYear & ", " & Me.Sales1Total & ", " & Me.Sales2Total & ")"

CurrentDb.Execute sSql, dbFailOnError

'this does assume that Me.ReportMonth will be inserted into
[Payments].[sum1].
Order is important. Also make sure that [Payment].[CheckDate] doesn't allow
duplicates. This way, if there already is a record with the same date, then
the insertion will fail.

HTH,
Willy

 

[Matt Reed]

Thanks Willy,

Worked great.

One more question, how can I make sure there is not already a record in the
table for the date CheckDate (first field in list), so that I do not
mistakenly enter two records for the same year, month.

In other words I need to be sure that there are not two entries for any
given month.

Thanks again.

Matt

Hi Matt,

first, I believe you can drop the ".Value", but not 100% certain
try this.... (and watch for line wrapping) *** untested ***

Dim sSql as String

sSql = "INSERT INTO Payments (CheckDate, sum1, sum2, sum3, TotSum) " & _
"Values (" & Format(Me.YourDateField, "\#mm\/dd\/yyyy\#") & ", " &
Me.ReportMonth & ", " & _
"" & Me.ReportYear & ", " & Me.Sales1Total & ", " & Me.Sales2Total & ")"

CurrentDb.Execute sSql, dbFailOnError

'this does assume that Me.ReportMonth will be inserted into
[Payments].[sum1].
Order is important. Also make sure that [Payment].[CheckDate] doesn't allow
duplicates. This way, if there already is a record with the same date, then
the insertion will fail.

HTH,
Willy