Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Wanet Telecoms Ltd on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

ODBC Updates 1

Status
Not open for further replies.

fabby1

Technical User
Mar 9, 2004
206
GB
Hi

Maybe I need to explain fully.

I have a SQLServer Database that I link to through linked tables in Access. this works fine.

I can see my tables an update them directly via datasheet if needed.

I have a form that contains the Main form and a Subform.

The main form is based on a simple query showing just name and staffno.

The subform shows all sickness relating to the employee.

The subform is linked via the staffno.

All this works OK.

I have on the main form a series on input boxes that the user enters sickness details into.

There is a ADD RECORD button.

What I want to do and can't is add the details from the input boxes into the subform.

How can I do this.

Here is the code so far on the ADD RECORD button

Code:
Private Sub AddRecord_Click()

    Dim rs As Object
    Dim rst As DAO.Recordset
    Dim StaffNum As Double
    
    StaffNum = Me.cmb_Search.Value

    Set rst = Me!SicknessMainFormSub.Form.Recordset
    
 With rst
    
   .AddNew
  ' ...
  ' ...
  ' ...
 End With
   
End Sub

Hope you can help

Thanks

Phil
 
Well, for starters, it looks like you are trying to use a DAO recordset. If that is the case, first make sure you have a reference (Tools -> References in the VBA window) set to DAO 3.x library (3.6 is most recent, I think, or at least the one for Windows 2000). Next, you may need your code to be something like this:

Code:
Private Sub AddRecord_Click()
    Dim db as DAO.Database
    Dim rs As DAO.Recordset
    Set db = CurrentDb
    Set rs = Me!SicknessMainFormSub.Form.Recordset

    Dim StaffNum As Double
    
    StaffNum = Me.cmb_Search.Value
    
 With rs    
   .AddNew
  ' ...
  ' ...
  ' ...
   .Update
 End With
   
End Sub

Just a suggestion - I've not used it for accessing just a recordset of a form, but usually for tables. Are you sure you are not pulling the data from tables into the form and subform?

Stephen [infinity]
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV
 
Stephen

Thanks for the post that helped me a long the way, got it sorted

Thanks

P
 
Glad could help!

Stephen [infinity]
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top