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

    Unanswered: problems linking forms

    I have two forms- Form3 ("main" form), and frmLayer, which is opened by a command button on Form3. frmLayer consists of several fields. LayerType is populated from a cbo on Form3. There is an invisible JobID field that I'm trying to use in the Query frmLayer is based on to only retrieve records in tblLayer that correspond to the JobID in Form3. I'm setting an input box to check to SQL stmt, and it grabs the right jobid... but for some reason, I can't get it to assign it to the box, so its throwing a record into the tblLayer table, but not creating the link to the specific jobid. Does that make sense? I'm attaching what I think is the relevant code...

    SQL Stmt in BeforeUpdate for frmLayer:
    strSql = "SELECT LayerID, cboLayerType, txtLayerNum, txtBrdWShort, txtShortDetected, " & _
    "txtQtyRepaired, txtBrdWOpen, txtOpensDetected, ckScrapCore " & _
    "FROM tblLayer WHERE JobID = " & Form_Form3.JobID & ";"

    DoCmd.OpenForm stDocName
    InputBox strSql, strSql, strSql
    Form_frmLayer.Form.RecordSource = strSql
    Form_frmLayer.Form.Requery

    SQL Stmt as it appears in InputBox:
    SELECT LayerID, cboLayerType, txtLayerNum, txtBrdWShort, txtShortDetected, txtQtyRepaired, txtBrdWOpen, txtOpensDetected, ckScrapCore FROM tblLayer WHERE JobID = 14;

    EventProcedure is in Before Update in Properties. I'm still getting #Name, and it creates a new LayerID each time instead of looking for JobID when I know a Layer record already exists for the given JobID...

    i'd appreciate as much help as I can get.

    Thanks!!
    Seren

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Seren,

    Since it doesn't work, I'll assume it's because JobID is string, not numeric, and SQL needs delimiters for string data. Therefore, try changing
    Code:
    strSql = "SELECT LayerID, cboLayerType, txtLayerNum, txtBrdWShort, txtShortDetected, " & _
    "txtQtyRepaired, txtBrdWOpen, txtOpensDetected, ckScrapCore " & _
    "FROM tblLayer WHERE JobID = " & Form_Form3.JobID & ";"
    to
    Code:
    strSql = "SELECT LayerID, cboLayerType, txtLayerNum, txtBrdWShort, txtShortDetected, " & _
    "txtQtyRepaired, txtBrdWOpen, txtOpensDetected, ckScrapCore " & _
    "FROM tblLayer WHERE JobID = '" & Form_Form3.JobID & "';"
    by simply adding single quotes before and after the JobID grab.

    If this works, good. However, I admit to being confused as to how you're using the results of the SQL statement. Are you trying to assign a text box with the result or are you using this in some other manner?

    Hope this helps,

    Sam

Posting Permissions

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