second form opens w/ no records

[Guest]

I have two forms. One calls the other with a command button. I am trying to
get it so that, when the 2nd form is opened, it is opened with a specific #
of records already created and ready to be *updated*. there is a field on
the first form that requests the number of layers for a job. That's how many
times it should loop through the for... next loop. I have an input box in
place that shows me it's grabbing the information from the forms, but the 2nd
form is coming up with no records.

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

 

[SteveS]

Hi Seren,

It took me a while to understand your code, but I think I've got it now.

The reason there are no records is that you assign a statement to the variable
strSQL, but you don't execute it. You need to use statements like

DoCmd.RunSQL

or

CurrentDB.Execute


I was very confused about the two InputBox statements; then I realized you were
using them to display the strSQL variable. Instead of InputBox, use the Msgbox
function

Msgbox strSQL


I would have the button (on Form3?) do the inserts. Then open the second form.


The record source for the second form ("frmLayer"?) would be a query:

SELECT tblLayer.LayerID, tblLayer.cboLayerType, tblLayer.txtLayerNum,
tblLayer.txtBrdWShort, tblLayer.txtShortDetected, tblLayer.txtQtyRepaired,
tblLayer.txtBrdWOpen, tblLayer.txtOpensDetected, tblLayer.ckScrapCore,
tblLayer.jobid FROM tblLayer;

And the code to open the form would be like:

DoCmd.OpenForm "frmLayer", , , "JobId= " & Me.tbJobID


where Me.tbJobID is the control on the first form that holda the Job Id number.


The code for the click event of the button on the main form would look
something like this:

'**********************************
Private Sub Command6_Click()
Dim vrowCount As Integer
Dim strSQL As String
Dim vJobId As Integer
Dim vcboLayerType As Integer
Dim x As Integer

'check to see if there are values in the controls
If IsNull(Me.tbJobID) Or IsNull(Me.cboLayerType) Or IsNull(Me.HowMany) Then
'open the form with ALL records
DoCmd.OpenForm "frmLayer"
Else
'holds the job id number
vJobId = Me.tbJobID
'holds the layer type number
vcboLayerType = Me.cboLayerType
'holds the number of rows to insert
vrowCount = Me.HowMany

For x = 1 To vrowCount
'create the SQL string
strSQL = "INSERT INTO tblLayer (JobID, cboLayerType, txtLayerNum) " & _
"VALUES (" & vJobId & ", " & vcboLayerType & _
", " & rowCount & ");"

'insert the new record
CurrentDb.Execute strSQL, dbFailOnError
Next x

'open the second form with the job id number
DoCmd.OpenForm "frmLayer", , , "JobId= " & Me.tbJobID
End If

End Sub
'************************************

I can't tell what is a field name, form name, or control name so you will have
to change my example to match your objects.


If you follow a naming scheme, it is easier writing code because you can tell
what type of object you dealing with.