Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369

    Unanswered: adding records to a table based on text field values and variables

    I want to log all exchange rate changes to the tbl_exch_rate_history table, at the same time that I update the only value in the tbl_current_exch_rate

    the history table has the default value of the time field set to now(), so I do not need to enter anything there from my form.

    I want to take the value just entered in the current form's txtexch_rate field, and put it both into the tbl_current_exch_rate table's only field, AND into the history table as follows:

    txtexch_rate goes into the exch_rate field, the current user name goes into the user field. I could not seem to set currentuser() as a default value for the field in the table itself, so I think I must do it by code.

    This should all happen by clicking a button.

    However, I am not sure how to make the update code needed, I think I must use a recordset from the history table, or what?

    Any advice/code examples most appreciated.

  2. #2
    Join Date
    Nov 2003
    Posts
    267
    If all you are doing is logging the exchange rate change, then you don't really need a recordset form the history table, just use ADO to add a new record to your history table put this code in the same button that you use to update the tbl_current_exch_rate.

    S-

  3. #3
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369
    Originally posted by sbaxter
    If all you are doing is logging the exchange rate change, then you don't really need a recordset form the history table, just use ADO to add a new record to your history table put this code in the same button that you use to update the tbl_current_exch_rate.

    S-
    is there a way to add a whole record without using a recordset? Could you provide a code example? When I look at ADO, recordsets seem to be the way to go there as well.

    I am not experienced with using code to add/update tables, and know a little too little about recordset usage. It seems to me at first sight to be a little overkill just to add values to specific fields in a new record in a table that have no required fields and to augomatically filled-in fields on the table level.

    I guess I am looking for a sample block of code to study and learn from, but I would like to know for sure that it fits my purpose - not just take any example without knowing if that is the best solution to my case... There is no problem finding sample recordset blocks...

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Originally posted by kedaniel
    is there a way to add a whole record without using a recordset? Could you provide a code example? When I look at ADO, recordsets seem to be the way to go there as well.

    I am not experienced with using code to add/update tables, and know a little too little about recordset usage. It seems to me at first sight to be a little overkill just to add values to specific fields in a new record in a table that have no required fields and to augomatically filled-in fields on the table level.

    I guess I am looking for a sample block of code to study and learn from, but I would like to know for sure that it fits my purpose - not just take any example without knowing if that is the best solution to my case... There is no problem finding sample recordset blocks...
    You can issue an INSERT QUERY using the ADO Ecute command of the Command object ...

    Ex: INSERT INTO MyTable ( MyField ) VALUES ( 1 );

  5. #5
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369
    Originally posted by M Owen
    You can issue an INSERT QUERY using the ADO Ecute command of the Command object ...

    Ex: INSERT INTO MyTable ( MyField ) VALUES ( 1 );
    ok, I guess here is where my knowledge of coding comes to a halt... I lack some understanding of the following general aspects:

    in the sub routine, should I start such a statement with for example "docmd.runsql "(insert into.... "" or what is the ADO Execute command and what is "command object" in this case? Does it mean the very docmd.runsql or what? I do not know all these phrases, I am afraid.

    Moreover, from that example, I would then write
    insert into tbl_exchange_rate (txtcurrent_exchange_rate) - but then - Values? Plural? Not only the value of the exchange rate field, which I normally write like this: field.value ? And what is the (1) at the end? I would think I should put all changes to the same record in the same insert into command, how do I put the currentuser() into its field using that same command and at the same time specifying the exch_rate?

  6. #6
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369
    Originally posted by M Owen
    You can issue an INSERT QUERY using the ADO Ecute command of the Command object ...

    Ex: INSERT INTO MyTable ( MyField ) VALUES ( 1 );
    how do one refer to (in order to save/add it to a second table) the record currently in memory, without the need to query a table or other? Is it possible without referring to a recordset?

  7. #7
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369
    fyi - example for other not-yet-gurus :-)

    - I now got it all working, and have the following code on the button_click event:

    (btw. Special thanks to JoeG for excellent help on this!)


    In a generally available module, declarations section:
    Public gcurrExchRate As Currency

    ***

    Private Sub cmdUpdateExchRate_Click()
    On Error GoTo Err_cmdUpdateExchRate_Click

    Dim strSQL As String
    Dim lcurrent_exch_rate As Long

    ' saving the current value onto the underlying one-cell table, tbl_current_exch_value:

    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

    ' gcurrExchRate is a public variable (currency type) declared in a module avaiable to all objects)

    ' updating/setting the public variable from the txt field

    gcurrExchRate = Me.txtcurrent_exch_rate1

    ' setting a general field on the main form to the value of the corresponding field on the subform. That field is visible when using the other subforms on this tab control. Convenient for the user.

    Me.Parent.txtCurrentExchRate2.Value = Me.txtcurrent_exch_rate1.Value

    ' then, when the value is saved/the underlying table is updated, an exchange rate history table is updated along with the name of the current user and a timestamp:

    lcurrent_exch_rate = Nz(Me.current_exch_rate, 0)

    If lcurrent_exch_rate > 0 Then

    strSQL = "INSERT INTO tbl_exch_rate_history ( ExchRate, [TimeStamp], User ) SELECT tbl_current_exch_rate.current_exch_rate, Now() AS [TimeStamp], CurrentUser() AS User FROM tbl_current_exch_rate "

    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True

    End If

    and then some error handling (still not properly finished, postponed - taking all the error handling at the end of the app dev., maybe not time saving, but want to take all that in one piece as I lack experience with it)

    ... End Sub.


    And finally, on the form_open event of the main form,
    in order to have the currently last saved value in
    the txt field instead of zero (before the first update is made), I look it up from the one-cell table:

    Me.txtCurrentExchRate2 = DLookup("current_exch_rate", "tbl_current_exch_rate")

    kedaniel :-)

  8. #8
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369
    Originally posted by kedaniel
    And finally, on the form_open event of the main form,
    in order to have the currently last saved value in
    the txt field instead of zero (before the first update is made), I look it up from the one-cell table:

    Me.txtCurrentExchRate2 = DLookup("current_exch_rate", "tbl_current_exch_rate")
    hm. I experience that *sometimes* (not always...) when opening the form right after opening Access , without having had the db open in that instance of Access earlier, the above lookup does not happen... If I then enter design view and go directly back to form view, it runs/works. When Closing Acces and reopening the db/form right afterwards, it works right away... What is this ? Is something left in computer memory when I reopen Access if not "too long" time has passed? Is it "working", or is it not... Maybe I should put the lookup ALSO in the form_current section/event, would that ensure it better?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •