Results 1 to 9 of 9
  1. #1
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Referencing Forms (97 - 2000)

    Hi, just a quickie, as soon as the search facility is back i will be using it LOADS, but till then sorry if this has already been asked:

    i got some code where i do

    Dim strTableName as String, intLastNo as Integer
    DoCmd.OpenForm (strTableName)
    DoCmd.GoToRecord acDataForm, strTableName, acLast
    intLastno = Forms!MYTABLE!DocId
    DoCmd.GoToRecord acDataForm, strTableName, acNewRec
    Forms!MYTABLE!DocId = intLastno + 1

    where strTableName picks up the value "MYTABLE" from the combobox.

    but how do i reference the table / form using the variable for the Forms!MYTABLE!DocId statements?

    Forms!(strTableName)! etc doesnt work

    thanks in advance
    Thanks,

    pmatz

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Referencing Forms (97 - 2000)

    Use Forms(strTableName) instead of Forms!(strTableName). For example:

    Forms(strTableName)!DocId = intLastno + 1

  3. #3
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Referencing Forms (97 - 2000)

    Thanks Hans,

    how do i do the same thing for a table field ref? i noticed that tables!MYTABLE!DocId doesnt work...

    is the way to do this to create a query then reference that?
    Thanks,

    pmatz

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Referencing Forms (97 - 2000)

    You can't directly refer to a field in a table or query if it is not part of the record source of the form. The question is, to which record in the table/query you want to refer? That is not clear if the field is not part of the record source.

  5. #5
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Referencing Forms (97 - 2000)

    Ah, well, what i want to do is simple enough.

    I want to go to the last record in my table, read the value of docid, then create a new record with an incremented docid and open this in a form for the user to edit.

    I can do it referencing the field in the form, but i want to stop the user from being able to scroll through the records in the form, so was thinking of getting the info before opening the form and then making the form 'cycle' current record etc.

    hope this makes sense. I cant use autonumber because it is just to problematic.

    thanks
    Thanks,

    pmatz

  6. #6
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Referencing Forms (97 - 2000)

    I dont know how to make the form so that ONLY the new record is editable, viewable...

    I can take off all the record selectors etc, but CTRL pageup and down still cycle through.

    Is there anyway of making this not the case? I dont want the user to be able to see any other record apart from the new one from this form.
    Thanks,

    pmatz

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Referencing Forms (97 - 2000)

    You can set the Data Entry property of the form to Yes; this means that the user will only see a new record. If you also set Allow Filters to No, the user won't be able to use the Records menu to display all records by removing the filter.

    Opening a form with DoCmd.OpenForm FormName:="MyForm", DataMode:=acDataAdd is equivalent to Data Entry = Yes.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Referencing Forms (97 - 2000)

    You can use DMax("DocID", "TableName") to retrieve the highest value of DocID in the table TableName, so you could use something like

    Me.DocID = DMax("DocID", "TableName") + 1

    in the On Load event of the form. See my other reply about methods to ensure that the user only sees a new record.

  9. #9
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Referencing Forms (97 - 2000)

    Cool, thats very helpful...

    You are most knowledgable when it comes to these MS apps and thier VB [img]/forums/images/smilies/wink.gif[/img]
    Thanks,

    pmatz

Posting Permissions

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