Results 1 to 3 of 3
  1. #1
    epardo87 is offline Competent Performer
    Windows 7 32bit Access 2016
    Join Date
    Dec 2016
    Posts
    101

    GoToRecord using fields in a form

    Hello there,



    So I have [frmMain] which has [txtStation], [txtEmployee], [txtScheduledDate] and button "btnSchedule"
    when click on "btnSchedule" form [frmEditSchedule] opens up, the data entered in this form will fill a table [tblSchedule]
    I wish to create a condition in the Form_Load of [frmEditSchedule] where if If [txtScheduledDate] is not Null, then it goes to the record matching [txtStation], [txtEmployee] and [txtScheduledDate].
    [tblSchedule] has columns [Employee], [Station] and [ScheduledDate] and a combination of employee with station can appear several times but always with different date...
    this is what I have so far, how could I make the form go to the record with that combination of fields?
    thanks!
    Code:
    Private Sub Form_Load()
    If IsNull([frmMain]![txtScheduledDate]) Then
    DoCmd.GoToRecord , , acNewRec
    Else
    DoCmd.GoToRecord ,????????????????
    End If End Sub

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Make a query using the fields as criteria, then open the query,
    docmd.openquery "qsMyQuery"

    if you have the key, open a form to that record,
    docmd.openForm "myForm",,,"[key]=" & me.key

  3. #3
    epardo87 is offline Competent Performer
    Windows 7 32bit Access 2016
    Join Date
    Dec 2016
    Posts
    101
    Thanks for your answer,

    I thought about your suggestion, I didn't understand it quite well because the Key would be changing every time, since the combination of operator, station and date can be selected by the user, the form should go to the table and find record with the key where that combination exists. Nevertheless that gave me an idea:

    • In query I created column [RowNum] with this calculation: RowNum: DCount("[ScheduleID]","[tblSchedule]","[ScheduleID]<=" & [ScheduleID])
    • Then in [frmMain] I created a textbox [txtRowNum] with this formula in control source:

    Code:
    =IIf(IsNull([cmbStation]),"",IIf([txtSchedule]="Nule","", DLookUp("[RowNum]","[Qry-ScheduledStatus]","([Station]='" & [cmbStation] & "') And ([Operador]='" & [lstDesig] & "') And ([ScheduledDate]=#" & [txtSchedule] & "#)")))
    • Finally in the Private Sub Form Load event of the [frmEditSchedule] I used this code:

    Code:
    If Forms!frmMain![txtSchedule] = "Nule" Then
    DoCmd.GoToRecord , , acNewRec
    Else
    DoCmd.GoToRecord , , acGoTo, Forms!frmMain!txtRowNum
    End If

Please reply to this thread with any new information or opinions.

Similar Threads

  1. GoToRecord acGoTo
    By Ruegen in forum Programming
    Replies: 1
    Last Post: 03-08-2015, 11:04 PM
  2. Replies: 5
    Last Post: 06-30-2014, 10:08 PM
  3. DoCmd.GoToRecord
    By Mtyetti2 in forum Access
    Replies: 3
    Last Post: 11-15-2013, 06:59 AM
  4. GoToRecord Help
    By rayhawk in forum Access
    Replies: 10
    Last Post: 08-30-2010, 12:44 PM
  5. GoToRecord Help!!!!!!
    By Kipster1203 in forum Access
    Replies: 8
    Last Post: 06-11-2010, 11:12 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums