Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Jul 2003
    Posts
    7

    Question Unanswered: Docmd, GoToRecord, , acNewRec - help!

    Hi i would be really grateful if anyone could help me out here..
    My problem is - i've been asked to alter a basic Access database, complete with forms and reports.
    It has been designed so the user can view previous and next records as they are stored in table 1
    This has been done using code like
    DoCmd.GoToRecord , , acPrevious

    I have been asked to create a search button so the user can search for data as opposed to hitting the previous button until they find what they are looking for.
    So, i have created a SQL statement to pull out whatever data - this works fine

    The problem is :
    When the user uses the search button, and then closes the form - whatever the record was that is displayed on the form, is copied again into table 1. - this produces an error message saying 'cannot create duplicate values' etc. ie cant have the same record in twice
    My code knows if the user is just hitting the previous/next commands (ie using the cmd goto acNext/Previous) or it is using the recordset used by my sql query.
    It doesnt save anything when the user is just browsing with the previous/next buttons, but as soon as the search command button is clicked - it saves when exiting.

    Why or how can i get round this? i cant see any other save/update code in the program

    Also i tried Private Sub Form_Close ()
    but when i put a break in at this , run the form, hit the X button, the error messages and such like come up before it actually triggers sub form_close ()


    Im baffled so any suggestions would be really welcomed and appreciated!!
    Last edited by dem; 07-11-03 at 06:06.

  2. #2
    Join Date
    May 2002
    Location
    London
    Posts
    87
    Well the standard combo box lookup is:

    Code:
    Private Sub ctlSelectRecord_AfterUpdate()
    ' Find the record that matches the control.
    Me.RecordsetClone.FindFirst "[RecordID] = " & Me![ctlSelectRecord]
    Me.Bookmark = Me.RecordsetClone.Bookmark
    End Sub
    Now if you can returrn the ID number from your seach you can use this code to go to the record provided by the search.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    dem,

    Make sure the combo box you use is not bound to the underlying table, in which case you are actually changing the primary key when you think you are moving to a new record.
    Otherwise, your code appears to be creating a new record, not saving changes to and existing record.

    This is one of the few areas where a microsoft Wizard does a job well. Add a combo box and when the wizard asks, choose "Find a record in my form based on the value I select."

    You'll get code essentially like Risky's example.

    blindman
    Last edited by blindman; 07-11-03 at 17:48.

  4. #4
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    caveat - the wizard will only show that option if the recordsource behind the form is a primarykey and you are seeking the record based on this key

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Didn't know that. Are you sure you can't use it to reference any unique index? Probably not, but you could always run it on your primary key and then change the resulting code to use a different unique index.

    blindman

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    the code behind the combo uses .findfirst - it will work (= not crash) with non-unique fields as combo.value but it's tough to think of a meaningful use for this capability.



    the combo wizard tries to handle the primary key question. if you select another field or fields to show in your combo, the wizard forces the primary key into the combo (you get the ? hide primary key prompt).

    the value of the combo is then the primary key but the display is whatever field(s) you selected.

    izy

  7. #7
    Join Date
    May 2002
    Location
    London
    Posts
    87
    I think that was from the wizard. Its also the basis for returning to the current record after you requery the record source. ecept its

    Dim lngMyID as Long
    lngMyID = RecordID

    Requery ' or whatever needs doing

    Me.RecordsetClone.FindFirst "[RecordID] = & RecordID
    Me.Bookmark = Me.RecordsetClone.Bookmark

  8. #8
    Join Date
    Jul 2003
    Posts
    7
    Hi, and thanks for all your replies!
    You've got me thinking about combo boxes now.
    I wasn't actually using that to search for the record, just a text box with the user inputing a date which was then inserted into the sql statement..
    The table im referencing is quite large though and has 3 fields set as primary key.
    If all these records were listed in the combo box would this not defeat the purpose of having a search button? - i'm not overly familar with combo boxes though..

    I've been trying the code suggested here and im still not havin a lot of luck

    Is there just some way of closing the recordset produced by the docmd so that when the user clicks the search buttons this wont still be open, and when the user closes the form, it wont try and save the current record?

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Use three combo boxes, each having a source that is a list of the distinct values for one of the three primary keys. Do not attach any event code to these combo boxes and make sure they are not bound to any table fields. Instead, add a button that resets the form's record source to an SQL query including your combo-box values as a filter, and then requeries the form.

    Also,
    You say the problem occurs when the user presses the search button. Could you cut and paste the code behind the search button's ON CLICK event for us?

    blindman

  10. #10
    Join Date
    Jul 2003
    Posts
    7
    Hi..still sitting here trying to figure this out
    If i use three combo boxes with distinct values, one will hold 4 values, another will hold roughly 365 days, and the other one will hold quite a lot too (its a time) so im not sure if this is the best way to go about it?

    My code for searching is :


    Boiler_No.SetFocus
    Boiler = Boiler_No.Text 'get the selected boiler number [1-4]

    If Boiler = "" Then
    MsgBox "Please select a Boiler number"
    Exit sub
    End If


    Dim date1 As String

    txtSearchDate.SetFocus
    date1 = txtSearchDate.Text

    If date1 = "" Then
    MsgBox "Please enter a date"
    Exit Sub
    End If



    SQL = "SELECT * FROM Boiler_Log WHERE Boiler_No = " & Boiler & " AND (Boiler_Log.Date) =# " & date1 & "#"

    On Error GoTo errtrap

    Set RecSet = Currentdb.OpenRecordset(SQL, dbOpenSnapshot)

    RecSet.MoveFirst
    Date.SetFocus
    Date.Text = RecSet!Date
    Time.SetFocus
    Time.Text = RecSet!Time
    Oil_Temp.SetFocus
    Oil_Temp.Text = RecSet!Oil_Temp
    Oil_Pres.SetFocus
    Oil_Pres.Text = RecSet!Oil_Pres
    Oil_Reading.SetFocus
    Oil_Reading.Text = RecSet!Oil_Reading
    Hours_Run.SetFocus
    Hours_Run.Text = RecSet!Hours_Run
    Start_Ups.SetFocus
    Start_Ups.Text = RecSet!Start_Ups
    Steam_Reading.SetFocus
    Steam_Reading.Text = RecSet!Steam_Reading
    Blow_Down.SetFocus
    Blow_Down = RecSet!Blow_Down
    Sight_Glasses.SetFocus
    Sight_Glasses = RecSet!Sight_Glasses
    Alarms.SetFocus
    Alarms = RecSet!Alarms
    Oil_Pump_Boiler_Filters.SetFocus
    Oil_Pump_Boiler_Filters = RecSet![Oil_Pump/Boiler_Filters]
    Cups.SetFocus
    Cups = RecSet!Cups

    Command120_Click 'calls execution code

    SearchDate = True

    '*** FOR PREVIOUS AND NEXT NAVIGATION***
    SQL = "SELECT * FROM Boiler_Log WHERE date >=#" & date1 & "# AND Boiler_No = " & Boiler
    SQL = SQL & " ORDER BY date"

    Set RecSet = Currentdb.OpenRecordset(SQL, dbOpenSnapshot)
    'enables the user to browse foward and back from the current date & boiler
    'they have searched for


    Exit Sub

    errtrap:
    Select Case Err.Number
    Case 3021
    MsgBox "There is no data for this date"
    Case 3075
    MsgBox "Please check the date is correct"

    Case Else
    MsgBox Err.Description & vbNewLine & " Error Number: " & Err.Number

    End Select

    When this form is opened it creates a new record

    Private Sub Form_Load()

    DoCmd.GoToRecord , , acNewRec 'when form displays create a new record

    Ive tried taking this out, and all references to the Docmd, but it still wants to save when im exiting

    I'll try combo boxes now maybe just to see if i can get something working!

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Then use a combo-box for the first field and masked textboxes or calendar controls for the other two.

    OK, now that I've looked at your code its my turn to be confused.
    First, you don't need to set focus on each control before setting its value, so stripping out all those lines should simplify your subroutine.

    Second, if (as is normal) the fields on your form are bound to a table or query as a recordsource, then when you open a second recordset in your code and start setting values you are actually trying to change the values in your form recordset's current record (or new record) to those of your subroutine recordset's current record, hence the problem.

    Try this code to change your form's recordsource instead:

    SQL = "SELECT * FROM Boiler_Log WHERE Boiler_No = " & Boiler & " AND (Boiler_Log.Date) =# " & date1 & "#"
    Me.Recordsource = SQL
    Me.Requery

    blindman

  12. #12
    Join Date
    Jul 2003
    Posts
    7
    Hi, sorry for the sloppy code! - i didnt realise you could fill the textboxes automatically like that! Im not too familar with Access
    I tried your suggested code and yes that will work to fill the textboxes, but i tried it with just the option to search by selecting the boiler number and it comes up with a runtime error '3022' '
    The changes you tried to make will created dupliated records, cannot save this time' etc' still

    I will have a fiddle around with what you suggested - thanks!

    [SIZE]
    Try this code to change your form's recordsource instead:

    SQL = "SELECT * FROM Boiler_Log WHERE Boiler_No = " & Boiler & " AND (Boiler_Log.Date) =# " & date1 & "#"
    Me.Recordsource = SQL
    Me.Requery

    blindman [/SIZE]

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    OK, but keep in mind that you are not filling the textboxes. That implies that you are adding or changing data, when what you want to do is search or filter the record source and DISPLAY the values in your form. Your code must still be trying to set some field values, and that is why you are getting the warning about duplicated records. Filtering or searching a recordset never adds or changes data and will never give this error.

    Look for statements where your code is setting values and eliminate them.

    blindman

  14. #14
    Join Date
    Jul 2003
    Posts
    7
    Hi again and thanks for all your advice!
    I've changed the code by taking out the setfocus refs.
    I'm still having major problems with selecting to view all the records for a specific boiler number
    My code to do this looks like :

    Private Sub cmdSearch_Click()


    Search = True

    Boiler_No.SetFocus
    Boiler = Boiler_No.Text 'get the selected boiler number

    If Boiler = "" Then
    MsgBox "Please select a boiler number"
    Exit Sub
    End If

    SQL = ""
    SQL = "SELECT * FROM Boiler_Log WHERE Boiler_No = " & Boiler
    SQL = SQL & " ORDER BY Date DESC"


    Me.RecordSource = SQL
    Me.Requery

    Command120_Click 'call calulation code

    End Sub

    Is there anything strange or wrong with this code??
    It seems to work when i select boiler one - but if i select a different number straight after, it says about not saving because would cause duplicates, so i presume its still trying to copy something back - but i'm only using the requery code now !!
    Like you said, searching a recordset never adds or changes data, so its just being odd..!

  15. #15
    Join Date
    Jul 2003
    Posts
    7
    Ah its ok i think i've sorted it - i still had the combo boxes bound to the underlying table!

    A final question - when the user searches for a record that does not exist the textboxes all go blank - how can i add a message box to say no record was found as opposed to it being blank and possibly making the user think they can input a new record now instead of hitting the new record button ?

Posting Permissions

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