Form/Subform to use same table exactly

  • Thread starter Thread starter Gavin
  • Start date Start date
G

Gavin

Hi
I am trying to make msaccess db for Computer Repair Job Tracking.

How do I make a form/subform, the main form has a joblist table showing one
customer at a time, and the subform has all records showing in a datasheet
view. This is so I can scroll down the datasheet and click on a record and
it will appear on the main main form in a normal view, as a single record.
The datasheet does not have to have all fields from the table, but it does
use the same table.
The datasheet is sort of like a menu of jobs, and the main form displays the
highligted row in greater detail.

The problem I get is on the datasheet I only see one row, ie one record
showing. That datasheet record and the main form record change when the
navigation button is pressed, but I want all the available records to show
in the datasheet subform, not just one. Can't find any answers on the web or
in help.

Any help appreciated.

Regards..Gavin Williamson
 
Gavin said:
Hi
I am trying to make msaccess db for Computer Repair Job Tracking.

How do I make a form/subform, the main form has a joblist table showing one
customer at a time, and the subform has all records showing in a datasheet
view. This is so I can scroll down the datasheet and click on a record and
it will appear on the main main form in a normal view, as a single record.
The datasheet does not have to have all fields from the table, but it does
use the same table.
The datasheet is sort of like a menu of jobs, and the main form displays the
highligted row in greater detail.

The problem I get is on the datasheet I only see one row, ie one record
showing. That datasheet record and the main form record change when the
navigation button is pressed, but I want all the available records to show
in the datasheet subform, not just one. Can't find any answers on the web or
in help.
Click to expand...

You can't use a normal Form-Subform setup for this with the
MasterLink-ChildLink properties. You need to clear those properties and use
code in the Current event of the SubForm to apply a filter to the main form. I
would personally use a ListBox for this rather than a SubForm, but I suppose it
would work either way.
 
Well the listbox worked easily, thanks very much Rick.

A couple of observations though:

1. My listbox has no scroll bar so I can't see all the records, and appears
to be no setting in the subform properties to activate them.
2. Also no headings, but I can fix that by using labels.

Can you suggest how to scroll down that listbox. Also just as a matter of
curiosity I how would I "use the code in the Current event of the Subform to
apply a filter to the main form." I am curious because I have been on
this one subform part for 2 days, and am interested in what you said here"


Regards..Gavin Williamson
 
Gavin said:
Well the listbox worked easily, thanks very much Rick.

A couple of observations though:

1. My listbox has no scroll bar so I can't see all the records, and appears
to be no setting in the subform properties to activate them.
2. Also no headings, but I can fix that by using labels.
Click to expand...

Do you mean a vertical scroll bar? In my experience there is no way NOT to have
a vertical scroll bar on a ListBox.

Can you suggest how to scroll down that listbox. Also just as a matter of
curiosity I how would I "use the code in the Current event of the Subform to
apply a filter to the main form." I am curious because I have been on
this one subform part for 2 days, and am interested in what you said here"
Click to expand...

Me.Parent.Filter = "[ID] = " & Me.ID
Me.Parent.FilterOn = True
 
Access displays a limited number of records in a listbox. You may need your
subform or a custom lookup form for this application.
 
Thanks Rick and Ron, the vert scroll not showing because I had the listbox
too wide off the screen, so I made it narrower and there is was, so the
listbox works pretty good, thanks for your help.

I have also been trying out the filter method and nearly got that going as
well, except when I scroll down to the last record the [ID] has no number in
it and I get an error

Run-time error '3075'
Syntax error (missing operator) in query expression '[JOBID]=

Can you look at this code for the subform:
Private Sub Form_Current()
Me.Parent.Filter = "[JOBID]=" & Me.JOBID
Me.Parent.FilterOn = True

' If Me.Parent.EOF Then Beep 'this doesnt work
' If Me.Parent.JOBID = 8 than Beep 'this beeps when I key down onto JOBID
no.8
If Me.Parent.JOBID.NextRecord = (AutoNumber) ' this doesn't work
Then goto me.parent.lastrecord ' I know this is right but how to make a
beep at the eof without any other errors

End If

It would be good if I could scroll up and down with no errors, may a beep at
bof and eof
Any ideas appreciated.

Regards..Gavin Williamson
 
Gavin said:
Thanks Rick and Ron, the vert scroll not showing because I had the listbox
too wide off the screen, so I made it narrower and there is was, so the
listbox works pretty good, thanks for your help.

I have also been trying out the filter method and nearly got that going as
well, except when I scroll down to the last record the [ID] has no number in
it and I get an error

Run-time error '3075'
Syntax error (missing operator) in query expression '[JOBID]=

Can you look at this code for the subform:
Private Sub Form_Current()
Me.Parent.Filter = "[JOBID]=" & Me.JOBID
Me.Parent.FilterOn = True

' If Me.Parent.EOF Then Beep 'this doesnt work
' If Me.Parent.JOBID = 8 than Beep 'this beeps when I key down onto JOBID
no.8
If Me.Parent.JOBID.NextRecord = (AutoNumber) ' this doesn't work
Then goto me.parent.lastrecord ' I know this is right but how to make a
beep at the eof without any other errors

End If

It would be good if I could scroll up and down with no errors, may a beep at
bof and eof
Any ideas appreciated.
Click to expand...

I don't understand. If the subform you are using to filter the parent is based
on the same table then there should be no cases where the main form goes to a
new record because there should always be a matching record. In what cases are
you ending up at a blank record?
 
No it doesn't go to a new record as such.
When I open the main form I can see one single record on the main data form
and all records in the sunbform. This is good.

When I click onto the subform datasheet at say record no.2, then the main
form information changes to show record No. 2 as well. This is exactly what
I wnt.

If I now scroll down the datasheet, I can get to the last genuine record
Record No. 10 (I only have 10 entries at the moment), but if I accidentally
scroll down one more, I am on a record that says (Autonumber) in the [JOBID]
field. (All the other fields are blank) In datasheet view this would be the
next available record that is waiting to be populated. If I do scroll down
to this record I get the error: Run-time error '3075' Syntax error (missing
operator) in query expression '[JOBID]=

So here is my new code for the subform

Private Sub Form_Current()
Dim rs As Object
Set rs = Me.Recordset.Clone
If rs.EOF Then
rs.MovePrevious
End If
Me.Parent.Filter = "[JOBID]=" & Me.JOBID
Me.Parent.FilterOn = True

But it doesn't work, I still get the error. So maybe I can hide the
Autonumber record from view and then I can't get to it. Or maybe my code is
wrong?

Regards..Gavin Williamson



Rick Brandt said:
Gavin said:
Thanks Rick and Ron, the vert scroll not showing because I had the listbox
too wide off the screen, so I made it narrower and there is was, so the
listbox works pretty good, thanks for your help.

I have also been trying out the filter method and nearly got that going as
well, except when I scroll down to the last record the [ID] has no number in
it and I get an error

Run-time error '3075'
Syntax error (missing operator) in query expression '[JOBID]=

Can you look at this code for the subform:
Private Sub Form_Current()
Me.Parent.Filter = "[JOBID]=" & Me.JOBID
Me.Parent.FilterOn = True

' If Me.Parent.EOF Then Beep 'this doesnt work
' If Me.Parent.JOBID = 8 than Beep 'this beeps when I key down onto JOBID
no.8
If Me.Parent.JOBID.NextRecord = (AutoNumber) ' this doesn't work
Then goto me.parent.lastrecord ' I know this is right but how to make a
beep at the eof without any other errors

End If

It would be good if I could scroll up and down with no errors, may a beep at
bof and eof
Any ideas appreciated.
Click to expand...

I don't understand. If the subform you are using to filter the parent is based
on the same table then there should be no cases where the main form goes to a
new record because there should always be a matching record. In what cases are
you ending up at a blank record?
Click to expand...
 
Gavin said:
No it doesn't go to a new record as such.
When I open the main form I can see one single record on the main data form
and all records in the sunbform. This is good.

When I click onto the subform datasheet at say record no.2, then the main
form information changes to show record No. 2 as well. This is exactly what
I wnt.

If I now scroll down the datasheet, I can get to the last genuine record
Record No. 10 (I only have 10 entries at the moment), but if I accidentally
scroll down one more, I am on a record that says (Autonumber) in the [JOBID]
field. (All the other fields are blank) In datasheet view this would be the
next available record that is waiting to be populated. If I do scroll down
to this record I get the error: Run-time error '3075' Syntax error (missing
operator) in query expression '[JOBID]=
Click to expand...

Set the Subform to AllowAdditions = No Then you won't get the last blank row at
all.
 
Yep, that fixed. Just what I wanted.

Thanks for your time Rick, much appreciated!

Regards..Gavin Williamson



Rick Brandt said:
Gavin said:
No it doesn't go to a new record as such.
When I open the main form I can see one single record on the main data form
and all records in the sunbform. This is good.

When I click onto the subform datasheet at say record no.2, then the main
form information changes to show record No. 2 as well. This is exactly what
I wnt.

If I now scroll down the datasheet, I can get to the last genuine record
Record No. 10 (I only have 10 entries at the moment), but if I accidentally
scroll down one more, I am on a record that says (Autonumber) in the [JOBID]
field. (All the other fields are blank) In datasheet view this would be the
next available record that is waiting to be populated. If I do scroll down
to this record I get the error: Run-time error '3075' Syntax error (missing
operator) in query expression '[JOBID]=
Click to expand...

Set the Subform to AllowAdditions = No Then you won't get the last blank row at
all.
Click to expand...
 
Back
Top