insesrt into table from field in form (1 Viewer)

megatronixs · Jun 2, 2017

Hi all,

I have a main form where there is a subform in datasheet view.
the first 2 fields are date fields. This is part of a function to get data from a client based on a start and end date and. When we used the tool in Excel, when you had multiple clients with the same date range, you could drag those cells down as much as you needed, but this does not work in Access.
I added a button on the main form and a textbox. In the text box I want to put the amount of how many times I want to duplicates those dates and add them below in the next empty record. I could do this with an Insert Query. but was wondering how to add the amount of times it has to do this.

I got my starting code to figure out what will work.
This is what I have so far:

Code:

Private Sub btn_duplicate_dates_Click()
'"tbl_batch_process" table where fields are
'"duplicte_times_txt" textbox to be used for multiply records
MsgBox Form!frm_batch_process!from_date_2 & " - " & Form!frm_batch_process!to_date_2
End Sub

any help with the amount of times I the texbox "duplicte_times_txt">

I should also mention that currently when I have one row selected (where the mouse is) the message box will show up with the dates I need for this to pass to the next row,
Greetings.

isladogs · Jun 2, 2017

Answering the first part of your question....

When we used the tool in Excel, when you had multiple clients with the same date range, you could drag those cells down as much as you needed, but this does not work in Access.

There is a way of repeating the previous record in Access
Click on a field in the table and press Ctrl+ ' on the keyboard.
The ' symbol is on the same key as @

However this only works for one field at a time

As for the main part of your question, you could use an update query for selected records

megatronixs · Jun 2, 2017

Hi Ridders,

Thanks for the tip, this is a good one

Greetings.

megatronixs · Jun 2, 2017

ok, I got a little bit further, just the dates are no correct now.
I go some where wrong:

Code:

Private Sub btn_duplicate_dates_Click()
    Dim db As DAO.Database
    Set db = CurrentDb
    Dim rs As DAO.Recordset
    Dim rst As DAO.Recordset
    Dim StrSQL As String
    Dim from_date As String
    Dim to_date As String
        from_date = Form!frm_batch_process!from_date_2
        to_date = Form!frm_batch_process!to_date_2
Set db = CurrentDb
    StrSQL = "INSERT INTO tbl_batch_process ( from_date_2, to_date_2 ) VALUES (" & "#from_date#" & "," & "#to_date#" & ");"
        db.Execute StrSQL, dbFailOnError
End Sub

Greetings.

megatronixs · Jun 5, 2017

Ok, I got closer. Now it enters the date, but it twist the date into a different format.
Can I set some how the date format to be dd/mm/yyyy in the code below?

Code:

'push the button to duplicate the dates in the sheet thing
Private Sub btn_duplicate_dates_Click()
    Dim db As DAO.Database
    Set db = CurrentDb
    Dim rs As DAO.Recordset
    Dim rst As DAO.Recordset
    Dim StrSQL As String
    Dim from_date As String
    Dim to_date As String
        from_date = Form!frm_batch_process!from_date_2
        to_date = Form!frm_batch_process!to_date_2
Set db = CurrentDb
    StrSQL = "INSERT INTO tbl_batch_process ( from_date_2, to_date_2 ) VALUES (#" & from_date & "#  , #" & to_date & "#);"
        db.Execute StrSQL, dbFailOnError
Form.Requery
Form.Refresh
End Sub

Greetings.

isladogs · Jun 5, 2017

megatronixs said:

Ok, I got closer. Now it enters the date, but it twist the date into a different format.
Can I set some how the date format to be dd/mm/yyyy in the code below?

Code:

'push the button to duplicate the dates in the [COLOR="Red"]sheet thing  !!!! [/COLOR]
Private Sub btn_duplicate_dates_Click()
  '  Dim db As DAO.Database [COLOR="red"]<= NOT NEEDED[/COLOR]
  '  Set db = CurrentDb [COLOR="red"]<= NOT NEEDED[/COLOR]
 '   Dim rs As DAO.Recordset [COLOR="red"]<= NOT NEEDED[/COLOR]
 '   Dim rst As DAO.Recordset [COLOR="red"]<= NOT NEEDED[/COLOR]
    Dim StrSQL As String
    Dim from_date As String
    Dim to_date As String
        from_date = Form[COLOR="red"]s[/COLOR]!frm_batch_process!from_date_2
        to_date = Form[COLOR="red"]s[/COLOR]!frm_batch_process!to_date_2
'Set db = CurrentDb [COLOR="red"]<= NOT NEEDED[/COLOR]
    StrSQL = "INSERT INTO tbl_batch_process ( from_date_2, to_date_2 ) VALUES (#" & from_date & "#[COLOR="red"] [/COLOR], #" & to_date & "#);"
        [COLOR="Red"]Current[/COLOR]db.Execute StrSQL, dbFailOnError
'Form.Requery [COLOR="Red"]<= PROBABLY NOT NEEDED [/COLOR]
'Form.Refresh [COLOR="red"]<= NOT NEEDED[/COLOR]
End Sub

This has to be one of the commonest questions on the forum.
In fact there were at least 2 other similar threads today alone.

Access tries to be clever with dates but often screws things up for anyone not using US date formats.
So it will convert e.g. 24/02/2017 successfully as it can't confuse that with a US date
However it will treat 04/02/2017 as 2nd April 2017.....

The safest solution is to first set your dates EXPLICITLY to "mm/dd/yyyy" format & then convert them back

Also, much of your code isn't needed e.g. recordset

Similarly you don't need to requery & refresh your form.
As you are appending into a table, I don't see why you need either of them
If my code doesn't work as it stands try reinstating Form.Requery

Also the syntax should be Forms! ..... NOT Form!

I've removed all unnecessary code below

Code:

Private Sub btn_duplicate_dates_Click()
    Dim StrSQL As String
    Dim from_date As String
    Dim to_date As String
    'set dates in mm/dd/yyyy format
        from_date = Format(Forms!frm_batch_process.from_date_2,"mm/dd/yyyy")
        to_date = Format(Forms!frm_batch_process.to_date_2,"mm/dd/yyyy")
 
   'convert dates back to dd/mm/yyyy again
    StrSQL = "INSERT INTO tbl_batch_process ( from_date_2, to_date_2 ) VALUES (#" & Format(from_date,"dd/mm/yyyy") & "#, #" & Format(to_date,"dd/mm/yyyy") & "#);"
        Currentdb.Execute StrSQL, dbFailOnError
  'Form.Requery 'USE ONLY IF NEEDED
End Sub

NOTE: Personally I prefer this syntax instead of VALUES ... but both work

Code:

StrSQL = "INSERT INTO tbl_batch_process ( from_date_2, to_date_2 ) SELECT #" & Format(from_date,"dd/mm/yyyy") & "#, #" &  Format(to_date,"dd/mm/yyyy") & "#;"

Finally, I strongly recommend you get rid of all underscores in table/form & field names as it makes things MUCH easier to code & to read as below:

Code:

StrSQL = "INSERT INTO tblbatchprocess ( fromdate2, todate2 ) SELECT #" & Format(fromdate,"dd/mm/yyyy") & "#, #" &  Format(todate,"dd/mm/yyyy") & "#;"

megatronixs · Jun 6, 2017

Hi Ridders,

Thanks a lot, it is working nice now.
the only thing I totally forgot about is, that the users will actually first enter 3 fields with data and then copy the new dates to the record. If I have already 3 records (branch nr and account nr, name), then, the dates will be placed in a row below as this will insert a new record. the first 2 fields have the "from date" and "to date".
Is there a way to enter the new dates into the row that has empty the first two fields?

Greetings.

isladogs · Jun 6, 2017

Not quite clear what you are asking so I can't promise the next bit is what you want.

I would use unbound text boxes for the 3 other fields
Then all you need to do is include these items in your append query along with the dates.

Alternatively if you want to use bound text boxes the record will be saved before handling the two dates. In which case, change the append query from my last post into an update query

insesrt into table from field in form (1 Viewer)

megatronixs

Registered User.

isladogs

MVP / VIP

megatronixs

Registered User.

megatronixs

Registered User.

megatronixs

Registered User.

isladogs

MVP / VIP

megatronixs

Registered User.

isladogs

MVP / VIP

Similar threads

Users who are viewing this thread