Open record from data sheet view ?????

[WILYWAYNE11]

I have been exploring a way to open a specific record in form view with a command from the data sheet view! This could be a time savings for our business. I have looked at creating a data macro but that doesn't seem to work either. Any suggestions? (PS..we are NOT engineers). Attached is a test database that we are experimenting with. I saw this same "feature" in a Access template database on line and it would be helpful if we could use it our large database as well

 

[Gasman]

Use OpenArgs argument on open form and pass in the ID of that record, or filter to just that record.
See DoCmd.OpenForm command

Why not inspect that DB and see which way they did it?

 

[bastanu]

Usually to navigate to a certain record from a datasheet it is customary to use the double-click event of a id or (customer, product, etc.) name field.

Cheers,

 

[theDBguy]

Hi. Can't look at your file right now but maybe take a look at the new Northwind 2.0 template for ideas on how they do something like this.

Sent from phone...

 

[lacampeona]

Hi
If I understand correctly i make some changes in your database.
I create for you new form in datasheet view. Then from this form you can open any ID record in form view.

See if this is what you need.
Best regards

 

[Pat Hartman]

You will save yourself a lot of time going forward if you adopt better naming conventions. For example Use CompanyID as the PK for tblCompany and then in tblJobs, use CompanyID or CompanyID_FK as the name for the foreign key. That way, you can look at names and easily work out what they are. You don't have to open the relationship diagram to figure out the relationship. And eliminate all spaces and special characters. Most people also hate the upper case names as well. I use CamelCase. That caps the first letter of each word. The nice thing about this is you don't have to shift as you type, you type camelcase, and as you move on, Access changes the word to CamelCase which helps you to catch typos as you go.

@lacampeona just posted the other change I was going to suggest. But I would remove the subform with the job data on it. You don't want that to be in two places.

 

[lacampeona]

Hi Pat
yes I also wanted to mentione that our new user Wily has to change all the namings in tables. I already change the id name.

About the subform I think we make that correct ?
From form in datasheet view users will click specific id (conpany id) and then in tab job records in the form- they will see see all the jobs created for that company iD?

​

​

 

[Pat Hartman]

@lacampeona I didn't check your solution carefully. It opened the correct record on the original form. That is as far as I went. I suggested that now that you have made a main form (which is what I would have suggested if you hadn't beat me to it) to list the jobs that the list that was an embedded subform be deleted since it is redundant.

 

[lacampeona]

Hi Pat
yes first time i was faster than you.

if i understand correctly what user want to do is...:
he have main form ..here he create id number company

and then inside he has a subform to create job records.,, I think is not redundand.

 

[isladogs]

You can't have command buttons on a datasheet form.
However there is a trick you can do to achieve this using a split form which has a single form section together with a datasheet.
You can hide the single form section leaving just the datasheet & have a command button in the form header. For example

This idea is also used in the new Northwind 2.0 example databases

 

[Pat Hartman]

Different forms serve different purposes. The point is to avoid like the plague having multiple forms that update the same data. So, either the main form version or the subform version should be read only. When you have multiple forms that update the same table, the best solution is to have each form's Beforeupdate event call a procedure to do the validation rather than doing it inside each BeforeUpdate event. You want to share the code. You don't want to duplicate it. It is quite easy to share the code if pass in a form reference.

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_Proc
    If CommonJobEdit(Me) = False Then
        Cancel = True
    Exit Sub
Exit_Proc:
    Exit Sub
Err_Proc:
    Select Case Err.Number
        Case 2501
            Resume Next
        Case Else
            MsgBox Err.Number & "--" & Err.Description, vbCritical
            Resume Exit_Proc
    End Select
End Sub
End If

Public Function CommonJobEdit(frm as Form) as Boolean
    If frm.CustName & "" = "" Then
        CommonJobEdit = False
        frm.CustName.SetFocus
        Msgbox "Customer name is required.",vbOKOnly
        Exit Sub
    End If
    If IsDate(frm.SomeDate) Then
        If frm.SomeDate > Date() Then
            CommonJobEdit = False
            frm.SomeDate.SetFocus
            Msgbox "Some Date must be <= to Today's date",vbOKOnly
            Exit Sub
        End If
    Else
        CommonJobEdit = False
        frm.SomeDate.SetFocus
        Msgbox "Some Date is required.",vbOKOnly
        Exit Sub
    End If

    CommonJobEdit = True    '''' No errors found   
End Function