Results 1 to 10 of 10
  1. #1
    Join Date
    May 2006
    Posts
    30

    Unanswered: Limit number of new records in 2nd form

    I'm not even sure if this is possible, but this is my idea-

    I have a form for Job information. Each job may, and usually does, contain more than 1 layer. I have a button at the bottom of the first form (Form2)that opens another form (frmLayer) for the user to enter the information for each particular layer. there is a field in the first form that asks for the number of layers. I want to either open the 2nd form with just this amount of records or only allow the user to add the # of records entered on the first form in that field. Make sense? Can this occur?

    TYVM!!
    Seren

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Yes it can.
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    May 2006
    Posts
    30
    haha... ok, fair enough. Any suggestions as to *how* to go about it, then?

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by serendee
    haha... ok, fair enough. Any suggestions as to *how* to go about it, then?
    You ask a question, you get an answer ..

    How I do this type of thing is to run some VBA code to insert balank records for the # required ...

    Perhaps a loop ...

    Oh, and turn off the Delete and Add records properties of the form ...
    Back to Access ... ADO is not the way to go for speed ...

  5. #5
    Join Date
    May 2006
    Posts
    30
    in order to use a loop to get the right number of records added, do I need to open a recordset and use the DAO? That's all somewhat foreign to me...

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by serendee
    in order to use a loop to get the right number of records added, do I need to open a recordset and use the DAO? That's all somewhat foreign to me...
    That depends on your requirements ... You don't need to for blank records (depending on how "blank" they are) ... For empty records you use a loop and Execute an INSERT query ...
    Back to Access ... ADO is not the way to go for speed ...

  7. #7
    Join Date
    May 2006
    Posts
    30
    Ok, with what I've got so far, I can get to the input box i use to make sure it's assigning everything properly... and it looks like it is. But, the 2nd form is coming up with no new rows when I know there is a number in form3.txtLayers. Here is the code:

    This is on the 2nd form:

    Function Layer()
    Dim stDup
    Dim JNum

    JNum = Form_Form3.JobID.Value
    stDup = "JobID = " & "'" & JNum & "'"
    ' If DCount("JobID", "tblLayer", stDup) > 0 Then

    Dim strSQL
    Dim strSql2
    Dim stDocName As String

    stDocName = "frmLayer"

    strSQL = "SELECT LayerID, cboLayerType, txtLayerNum, txtBrdWShort, txtShortDetected, " & _
    "txtQtyRepaired, txtBrdWOpen, txtOpensDetected, ckScrapCore,jobid " & _
    "FROM tblLayer WHERE JobID = " & Form_Form3.JobID & ";"

    strSql2 = "SELECT cboLayerType FROM tblLayer WHERE jobid = " & Form_Form3.JobID


    DoCmd.OpenForm stDocName
    InputBox strSQL, strSQL, strSQL
    Form_frmLayer.Form.RecordSource = strSQL
    Form_frmLayer.Form.Requery
    End Function

    Private Sub Form_Load()
    Layer

    Dim rowCount As Integer
    Dim strSQL As String

    rowCount = Form_Form3.txtLayers

    For x = 1 To rowCount
    strSQL = "INSERT INTO tblLayer (JobID, cboLayerType, txtLayerNum) " & _
    "VALUES (" & Form_Form3.JobID & ", " & Form_Form3.cboLayerType & _
    ", " & rowCount & ");"
    InputBox strSQL, strSQL, strSQL
    Next x
    End Sub

    Any suggestions as to why this is occuring and how to fix it?

    Thanks
    Seren

  8. #8
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Where do you either Execute (hint hint) or RunSQL your INSERT statement??? I see a loop ...
    Back to Access ... ADO is not the way to go for speed ...

  9. #9
    Join Date
    May 2006
    Posts
    30
    Thanks for the hint I'll play with that for a while.

  10. #10
    Join Date
    May 2006
    Posts
    30
    Ok, I'm getting closer... I can tell. (And then I have other issues to deal with!) But one thing at a time...

    I added the docmd.runsql (strsql) line. It seems to be attempting to run it now. (ty)

    I'm trying to get the text in the cbo from the 1st form into a field in the second form. At first, I was getting an error that said something along the lines of not being able to set control cboLayerType because it didn't have focus. Ok, so I added a line to set focus to that control. Then it said "Microsoft Office Access can't move the focus to the control cboLayerType." So how do I get that value out of the combo box on the first form and into the field in the second form?

    Thanks!
    Seren
    Last edited by serendee; 05-24-06 at 09:41.

Posting Permissions

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