Create Table from Form data

[DCV0204]

Hi, I have a Form named "RA_Processing" containing an unbound textbox named "DueDate" to calculate a due date based on other data on the form it is linked to Table "RtnRA"; on open the find box opens allowing me to search for a particular id.



I would like to be able to click button "btn60" and create a new table named "MergeData", which contains 3 fields ("RA_ID", "DueDate", "CNotif". this table would be overridden each time to only contain the data for that particular id.

Is creating a table from form data possible?
Thanks.

[ssanfu]

Yes, but it would be much better to create the table (manually) once.
The code for the button click event could delete any existing data and append the new data.

[DCV0204]

thanks for your reply...
I got
DoCmd.RunSQL "DELETE * FROM MergeData;"
to delete the records.

What do I use to append the new information?
I am stuck.
Thanks.

[ssanfu]

DoCmd.RunSQL "DELETE * FROM MergeData;"

This will work but I think it gives you a dialog message box about how many records will be deleted.
I use

CurrentDb.Execute "DELETE * FROM MergeData;", dbfailonerror

search for a particular id.

Will you append 1 record or will there be many records?

What is an example of the data?

What are the control names on the form?

What are the field types:
"RA_ID" -- Long Integer?
"DueDate" -- Date/Time?
"CNotif -- Text?

[DCV0204]

Always 1 record.

"RA_ID" - Text
"DueDate" - Date/UnboundTextbox
"CNotif" - Date


Data Example
RA_ID DueDate CNotif
1234567 11/28/2013 10/28/2013

Thanks.

[ssanfu]

RA_ID DueDate CNotif

What are the control names?

[ssanfu]

Here is air code for your button:

Option Compare Database  '< should be at the top of every module
Option Explicit    '< should be at the top of every module

Private Sub btn60_Click()
   On Error GoTo Err_btn60_Click

   Dim sSQL As String

   'delete data in table
   CurrentDb.Execute "DELETE * FROM MergeData;", dbFailOnError

   'insert new record
   sSQL = "INSERT INTO MergeData (RA_ID, DueDate, CNotif)"
   sSQL = sSQL & " VALUES ('" & Me.RA_ID & "', #" & Me.DueDate & "#, #" & Me.CNotif & "#);"
   CurrentDb.Execute sSQL, dbFailOnError


Exit_btn60_Click:
   Exit Sub

Err_btn60_Click:
   MsgBox Err.Description
   Resume Exit_btn60_Click

End Sub

Change the text in BLUE to your control names.

[DCV0204]

Steve, here is my code

Private Sub insertbtn_Click()
On Error GoTo Err_insertbtn_Click()
Dim sSQL As String
Dim DueDate As Date
Forms!RA_Processing.SetFocus
DoCmd.GoToControl "Medicaid_ID"

'delete data in table
'CurrentDb.Execute "DELETE * FROM MergeData;", dbFailOnError

'insert new record
sSQL = "INSERT INTO MergeData (Medicaid_ID, DueDate, CAQH_Notification)"
sSQL = sSQL & " VALUES ('" & Returned_Mail.Medicaid_ID & "', #" & RA_Processing.CalDueDate & "#, #" & Returned_Mail.CAQH_Notifification & "#);"
(I am getting a Object defined error on this line. What am I missing?)
CurrentDb.Execute sSQL, dbFailOnError

Exit_insertbtn_Click
Exit Sub
Err_insertbtn_Click
msgbox Err.Description
Resume Exit_btn60_Click
End Sub

Thanks.

[ssanfu]

On Error GoTo Err_insertbtn_Click()

Should not have the parenthesis after click

Exit_insertbtn_Click

and

Err_insertbtn_Click

These are Labels and REQUIRE a colon at the end for the "On Error GoTo Err_insertbtn_Click" statement to function.

Exit_insertbtn_Click:
     Exit Sub

Err_insertbtn_Click:

What is

Returned_Mail.Medicaid_ID

It looks like a "TableName.FieldName". I asked (twice) what the names of the controls are because you are getting the values from the CONTROLS, not fields in a table.

Since the controls are on the same form where the code is, you can use the "Me" shortcut to refer to "Forms!FormName".
When yo create a bound control on a form, Access tries to be helpful and names the control the same name as the field; it gets confusing sometimes. I always rename the control by adding a prefix or suffix. If the control is unbound, say a text box, then it gets named "Text10" or a button "Command9". Not very descriptive. Again I take the time to give meaningful names to the controls.



Back to your code problem. If the name of the CONTROL is "Medicaid_ID", use "Me.Medicaid_ID". (same for the other two controls)

   sSQL = sSQL & " VALUES ('" & Me.Medicaid_ID & "', #" & Me.CalDueDate & "#, #" & Me.CAQH_Notifification & "#);"

Private Sub insertbtn_Click()
   On Error GoTo Err_insertbtn_Click

   Dim sSQL As String
   Dim DueDate As Date

   Forms!RA_Processing.SetFocus
   DoCmd.GoToControl "Medicaid_ID"

   'delete data in table
   'CurrentDb.Execute "DELETE * FROM MergeData;", dbFailOnError

   'insert new record
   sSQL = "INSERT INTO MergeData (Medicaid_ID, DueDate, CAQH_Notification)"
   sSQL = sSQL & " VALUES ('" & Me.Medicaid_ID & "', #" & Me.CalDueDate & "#, #" & Me.CAQH_Notifification & "#);"
   (I am getting a Object defined error on this line. What am I missing?)
   CurrentDb.Execute sSQL, dbFailOnError

Exit_insertbtn_Click:
   Exit Sub

Err_insertbtn_Click:
   MsgBox Err.Description
   Resume Exit_btn60_Click
End Sub

   Forms!RA_Processing.SetFocus
   DoCmd.GoToControl "Medicaid_ID"

I don't understand why you have these two lines.

The form already is active (has the focus).
And to set the focus to a control, use

Me.Medicaid_ID.SetFocus

Or maybe I just don't understand the problem..........