After_update on subform

[cmco]

I have a form frmJobs which contains a subform sfrmCustomers. frmJobs has a
combo box whose sourse is the JobName field in tblJobs. The subform has a
combo bo whose sourse is the CustName field in tblCustomers. When a job is
selected in cboJobName the related customer information is displayed in the
sfrmCustomers. The two tables are related via a common CustomerID field in
both tables.

Two questions: cboJobeName did not show the new record in tblJobs so I hd to
add a NewRecord cmdButton to create a blank record for selection in the combo
box. Is there a way to have the new blank record automatically displayed in
the combo box each time a record is added; preferrably at the beginning of
the dropdown list rather than at the bottom? This is also the same problem
with the cboCustomers dropdown list.

Second Problem; When a new record is added in the Jobs form and I then
select a customer(existing) from the subforms cboCustomers the Main Jobs form
changes to the first Job with that customer. How do I get the Jobs form to
stay on the new Jobs record? I am using the FindFirst function in the
after_update event of cboCustomers as follows. I suspect that there is a more
correct approach.

Private Sub cboCustomerID_AfterUpdate()
Dim srs As Recordset, Criteria As String

Set srs = Me.RecordsetClone
Criteria = "[CustomerID] = " & Me.cboCustomerID
srs.FindFirst Criteria
If Not srs.NoMatch = True Then
Me.Bookmark = srs.Bookmark
End If

End Sub

Would someone help this obvious novice?

 

[Steve Schapel]

Cmco,

It seems to me that you have the design of the forms muddled up. Am I
correct in my understanding that there can be more than one Job for any
given Customer, but there will only be one Customer for any given Job?
If so, the Jobs form should be a subform on the Customers form, not the
other way around. There are other issues here as well, but let's get
this one clarified for a start.

 

[cmco]

You are right, a customer can have many jobs and a job only one customer.
However Jobs is my main database and my main form. I primarily enter the
application to create and track Job information. The jobs form has several
sub forms which show the customer, the end user, and the supplier of the
equipment. I also have several pages(tabs) for this form to list the critical
components and dates for the job. So I am confused. Possibly I should not use
a subform for the customer. My wish was to create the job information then
lookup a past customer or create a new customer for the job. Am I totally
lost in my approach? I appreciate your help.

Cmco,

It seems to me that you have the design of the forms muddled up. Am I
correct in my understanding that there can be more than one Job for any
given Customer, but there will only be one Customer for any given Job?
If so, the Jobs form should be a subform on the Customers form, not the
other way around. There are other issues here as well, but let's get
this one clarified for a start.

--
Steve Schapel, Microsoft Access MVP

I have a form frmJobs which contains a subform sfrmCustomers. frmJobs has a
combo box whose sourse is the JobName field in tblJobs. The subform has a
combo bo whose sourse is the CustName field in tblCustomers. When a job is
selected in cboJobName the related customer information is displayed in the
sfrmCustomers. The two tables are related via a common CustomerID field in
both tables.

Two questions: cboJobeName did not show the new record in tblJobs so I hd to
add a NewRecord cmdButton to create a blank record for selection in the combo
box. Is there a way to have the new blank record automatically displayed in
the combo box each time a record is added; preferrably at the beginning of
the dropdown list rather than at the bottom? This is also the same problem
with the cboCustomers dropdown list.

Second Problem; When a new record is added in the Jobs form and I then
select a customer(existing) from the subforms cboCustomers the Main Jobs form
changes to the first Job with that customer. How do I get the Jobs form to
stay on the new Jobs record? I am using the FindFirst function in the
after_update event of cboCustomers as follows. I suspect that there is a more
correct approach.

Private Sub cboCustomerID_AfterUpdate()
Dim srs As Recordset, Criteria As String

Set srs = Me.RecordsetClone
Criteria = "[CustomerID] = " & Me.cboCustomerID
srs.FindFirst Criteria
If Not srs.NoMatch = True Then
Me.Bookmark = srs.Bookmark
End If

End Sub

Would someone help this obvious novice?

 

[Steve Schapel]

Cmco,

No, a subform is not a good idea in this scenario. A subform is most
useful when it relates to information on the "many" side of a
one-to-many relationship with the data in the main form. If your
purpose here is to have customer-related data visible on the Jobs form,
maybe this article will help to suggest a more suitable approach...
http://accesstips.datamanagementsolutions.biz/lookup.htm

 

[cmco]

Thanks for the reference. I will study and revise my approach accordingly.