New record with similar data

[pbaldy]

Fixing Adam's code is fairly easy. Add this line at the beginning:

Dim strSQL As String

Add this line before going to a new record:

strSQL = "SELECT * FROM Orders WHERE OrderID = " & Me.orderid

and change the Set line to

Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

I believe that should work.

 

[james_IT]

Fixing Adam's code is fairly easy. Add this line at the beginning:

Dim strSQL As String

Add this line before going to a new record:

strSQL = "SELECT * FROM Orders WHERE OrderID = " & Me.orderid

and change the Set line to

Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

I believe that should work.

Thanks, but still not working. Now the command button opens a blank form..

My code is

Private Sub cmdFill_Click()


On Error Resume Next
Dim strSQL As String

strSQL = "SELECT * FROM tblTicketing WHERE StockNumber = " & Me.StockNumber
  DoCmd.GoToRecord acDataForm, Me.Name, acNewRec

Dim c As Control
  Dim rs As Recordset
    Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

    Me.Painting = False

      For Each c In Me.Controls
        If Not TypeOf c Is CheckBox Then
          If Not TypeOf c Is Label Then
            If Not TypeOf c Is CommandButton Then
              If Me.Controls("chk" & c.Name) = -1 Then
                Debug.Print c.Name
                c = rs(c.ControlSource)
              End If
            End If
          End If
        End If
      Next c
   
    Me.Painting = True

  rs.Close

Set rs = Nothing
End Sub

 

[pbaldy]

I tested in Adam's sample, and it worked as expected. I can only assume you didn't set yours up in a similar manner (the relationship between the checkboxes and textboxes).

 

[james_IT]

Its working if i amend adams version but not on mine! THe only thin i changed is the table name, and the index ID...

 

[pbaldy]

Without seeing it, not sure what to tell you.

 

[james_IT]

I have attached a scaled down version of my DB.

Any thoughts?

Thanks

 

[pbaldy]

For starters, get rid of this:

On Error Resume Next

You want to know when you get an error. Because your stock number is text, you need this:

strSQL = "SELECT * FROM tblTicketing WHERE StockNumber = '" & Me.StockNumber & "'"

You will then get a flock of errors about mismatches between the names, but the fields with matching names will work. The spaces and symbols in your field names are not a good idea, and will likely cause you problems in the long run.

 

[james_IT]

perfect! Thanks Paul!

Unfortunately the company have all their current paper datatbases and stocks set out in this format so im having a hard time convincing them to change it to solid numbers.

It works for the time being...

Thanks again

 

[pbaldy]

No problem. I should have clarified that I would have a "proper" error trap.