Copy data from textbox on form to multiple fields in table

  • Thread starter Thread starter shmoussa
  • Start date Start date
S

shmoussa

Okay I hope someone can help. I have a form with an import button.
Clicking this button opens up another form which allows the user to
type in any date and then click the continue button which continues
the import process. The .CSV file that is being imported has four
fields. The table that the data is being imported to has five fields.
The field that remains empty in the table is called DATE. Can someone
tell me how to fill the 200+ empty DATE fields using the date the user
types in?

Hopeful outcome: User clicks import enters 05/27/2007 in the date
field and clicks import. If the user does not type a date in, a
message box should inform the user and stop the import process.
Otherwise, 350 records are added to a table called DISKS. The DATE
field for each of the 350 records is filled in with 05/27/2007. Can
anyone give me the code to add to my already existing code? (My
importing code)

I'd appreciate it


********************************CODE
START***************************************
Private Sub Command3_Click()
On Error GoTo err_Command3_Click

Dim strFile As String
Dim str As String

strFile = GetOpenFile_CLT("C:\Disk Space", "Select the .csv file
that you want to import")

If strFile = "" Then
Exit Sub
Else
If MsgBox("Add the following file to your data: " & strFile, vbYesNo,
"Confirm the file is correct:") = vbNo Then
Exit Sub
Else
End If
End If


DoCmd.TransferText acImportDelim, "DISKS", "TableNew", strFile, True

****(I imagine the code is added here to copy the data in TextBox1 to
the Date field in the Disks Table)**********

exit_Command3_Click:
Exit Sub

err_Command3_Click:
MsgBox Err.Number & " " & Err.Description
Resume exit_cmdOpenFile_Click

End Sub

End Sub
**********************************************************************

Any help is appreciated. Thanks.
 
If you will always have a date for the blank field, you could run an update
query after the successful
transfer Text command...

update DISKS set Date = " & USER_ENTERED_DATE & " where Date is null"

HTH,

Corey

*************************CODE START***************************************
Private Sub Command3_Click()
On Error GoTo err_Command3_Click .....
DoCmd.TransferText acImportDelim, "DISKS", "TableNew", strFile, True
Click to expand...

*** RUN THE UPDATE QUERY HERE ***
 
shmoussa said:
Okay I hope someone can help. I have a form with an import button.
Clicking this button opens up another form which allows the user to
type in any date and then click the continue button which continues
the import process. The .CSV file that is being imported has four
fields. The table that the data is being imported to has five fields.
The field that remains empty in the table is called DATE. Can someone
tell me how to fill the 200+ empty DATE fields using the date the user
types in?

Hopeful outcome: User clicks import enters 05/27/2007 in the date
field and clicks import. If the user does not type a date in, a
message box should inform the user and stop the import process.
Otherwise, 350 records are added to a table called DISKS. The DATE
field for each of the 350 records is filled in with 05/27/2007. Can
anyone give me the code to add to my already existing code? (My
importing code)

I'd appreciate it


********************************CODE
START***************************************
Private Sub Command3_Click()
On Error GoTo err_Command3_Click

Dim strFile As String
Dim str As String

strFile = GetOpenFile_CLT("C:\Disk Space", "Select the .csv file
that you want to import")

If strFile = "" Then
Exit Sub
Else
If MsgBox("Add the following file to your data: " & strFile, vbYesNo,
"Confirm the file is correct:") = vbNo Then
Exit Sub
Else
End If
End If


DoCmd.TransferText acImportDelim, "DISKS", "TableNew", strFile, True

****(I imagine the code is added here to copy the data in TextBox1 to
the Date field in the Disks Table)**********

exit_Command3_Click:
Exit Sub

err_Command3_Click:
MsgBox Err.Number & " " & Err.Description
Resume exit_cmdOpenFile_Click

End Sub

End Sub
**********************************************************************

Any help is appreciated. Thanks.
Click to expand...

How about just updating your table wherever the Date field is Null:

DoCmd.RunSQL "UPDATE [TableNew] SET [Date]=Date WHERE [Date] IS NULL;"

One suggestion: change the name of the date field in the table. It's not
usually a good idea to name a field the same as a reserved keyowrd - in this
case, Date. Date is an Access method that returns the current date.

Carl Rapson
 
Thanks a lot. That did it. However I want a popup to confirm that the
date typed in is correct, as well as a popup to stop everything if
there is no date put in

I tried this but it is not working:

If Me.Text1 Is Null Then
MsgBox "Please enter a date.", , "No Date Entered"
End If

If MsgBox("Is this the correct date: " & Me.Text1, vbYesNo, "Confirm
that this is the correct date:") = vbNo Then
Exit Sub
Else
End If

Could you please help correct this? Thank you.
 
Thanks a lot. That did it. However I want a popup to confirm that the
date typed in is correct, as well as a popup to stop everything if
there is no date put in
Click to expand...

Try:

If IsNull(Me.Text1) Then
MsgBox "Please enter a date.", , "No Date Entered"
End If

If MsgBox("Is this the correct date: " & Me.Text1, vbYesNo, "Confirm
that this is the correct date:") = vbNo Then
Exit Sub
Else

<do something to open your popup form>

End If


John W. Vinson [MVP]
 
Okay. That's perfect thank you so much for your help.

Now, here's a question: If the user enters a date that already exists
on my table, can I stop the code?

Hopeful outcome: User clicks import, enters 05/14/2007 in the date
textbox on my form, however there is already data in my table
"TableNew" with the date 05/14/2007. So after the user clicks IMPORT,
a popup will says "Data already exists for 05/14/2007" and then stop
the code. Is this possible?

Thanks
 
Hopeful outcome: User clicks import, enters 05/14/2007 in the date
textbox on my form, however there is already data in my table
"TableNew" with the date 05/14/2007. So after the user clicks IMPORT,
a popup will says "Data already exists for 05/14/2007" and then stop
the code. Is this possible?
Click to expand...

Just use DLookUp to see if there is a record in the table for that date:

If IsNull(DLookUp("[Datefield]", "[TableNew]", "[Datefield] = #" & _
Me!txtDate & "#") Then
<proceed with the import>
Else
MsgBox "This import has already been done", vbOKOnly
End If

John W. Vinson [MVP]
 
Back
Top