Split Forms and ADP issues

[Vikki]

I have an (exisitng) ADP database. I know that there are things that you can
and cannot do with an ADP, but I don't know what they are. Any suggestions
on where to brush up on that would be appreciated.

I need to create a form where users can update a product promotion category
on several records (product briefs) at a time. They don't want to have to
enter each product brief, add/change/delete the catagories, then move on to
the next product brief and repeat. They want to bring up all product briefs
that meet a date range and update at will--kind of like 'spreadsheet style'.
The "field" they want to update is a many-to-many relation type field called
product promotions. This "field" is actually another table that contains the
key fields for the product brief and the promotion table. There may be
multiiple promotions for a product brief. Also, I have an originator (as
orgntr_rsrc_id) stored in the product brief table. The originator's first
and last name is stored in the resource table with rsrc_id as the key.

I have 2 unbound fields on the form that the user maintains to setup the
date range parameters to filter the records that are returned. They are
txtFromDate and txtToDate.

I have tried to accomplish this 2 different ways, one using the product
brief table as a record source and applying a serverfilter and the other
using a stored procedure as a record source, passing the dates as parameters.
In both cases the product promotions are handled with a subform and that
peice works.

Method 1:
I use the product brief table as the forms record source. The user enters
the begin and end dates and click a "search" button which runs the following
code to apply a serverfilter.

Private Sub Command213_Click()
If IsNull(Me.txtFromDate) Then
fromdate = #1/1/1900#
Else
fromdate = Me.txtFromDate.Value
End If

If IsNull(Me.txtToDate) Then
todate = #1/1/1900#
Else
todate = Me.txtToDate.Value
End If

Me.ServerFilter = "rtl_lnch_dt between '" & CStr(fromdate) & "' and '" _
& CStr(todate) & "' Or smpl_prgms_dt between '" & CStr(fromdate) & "' and '" _
& CStr(todate) & "'"
Me.Refresh
End Sub

This code is ok, and technically accomplishes what the user wants EXCEPT
they want the resource name, rather than Id, to appear. I have tried the
following select in the control source of the Originator text box but I get
#Name returned. Spellings are correct--if I copy this select over to the
server (MS SQL Server Management Studio), remove the parenthesis and change
the &'s to +'s it runs just fine

=(SELECT isnull([dbo].[rsrc_t].[rsrc_first_nm]
&''&[dbo].[rsrc_t].[rsrc_last_nm],' ') From [dbo].[rsrc_t] inner join
[dbo].[prdct_brief_t] on
[dbo].[rsrc_t].[rsrc_id]=[dbo].[prdct_brief_t].[orgntr_rsrc_id])

Maybe I can't do this in an ADP???

Method 2:
The control source for the form is:
exec pd_upd_NPI_p '3/1/2009', '3/31/2009'

where the 2 dates are the parameters for the beginning and ending date
ranges. The stored procedure correctly formats the Originator Name. It
opens with the correct data for the dates hard coded in the record source. I
am able to modify the records as desired. Seems Perfect! Then I change the
dates on the form to 10/01/2008 and 10/31/2008 and click my search button to
perform the following code:
Private Sub cmdRtrvRcrds_Click()
Dim fromdate As Date
Dim todate As Date

If IsNull(Me.txtFromDate) Then
fromdate = #1/1/1900#
Else
fromdate = Me.txtFromDate
End If

If IsNull(Me.txtToDate) Then
todate = #1/1/1900#
Else
todate = Me.txtToDate
End If

Me.RecordSource = "exec pd_upd_NPI_p '" & CStr(fromdate) & "', '" &
CStr(todate) & "'"

This code will then show the correct records for the October date range.
However, when I click on any of the records in the datasheet view, one of two
things will happen. I may get a message on the first click that says "One of
your parameters is invalid" with no error number and OK as the only option. I
click OK and select a different record (or click on the same record again)
and Access will hang-[Not Responding]. I have to alt-ctrl-del to shut it
down.
The other thing that may happen is really the same thing, but I don't get
the parameter message at all--I go immediately to not responding.

I am wondering if the resync command may be the key here. I have no idea
how to use it--I have done some searches, but haven't gotten a warm fuzzy
feeling that I know how to use it. If resync isn't the answer--any other
suggestions would be welcome.

...and yes, it has to stay a "project" because the boss says so.

 

[Sylvain Lafontaine]

I have an (exisitng) ADP database. I know that there are things that you
can
and cannot do with an ADP, but I don't know what they are. Any
suggestions
on where to brush up on that would be appreciated.

I need to create a form where users can update a product promotion
category
on several records (product briefs) at a time. They don't want to have
to
enter each product brief, add/change/delete the catagories, then move on
to
the next product brief and repeat. They want to bring up all product
briefs
that meet a date range and update at will--kind of like 'spreadsheet
style'.
The "field" they want to update is a many-to-many relation type field
called
product promotions. This "field" is actually another table that contains
the
key fields for the product brief and the promotion table. There may be
multiiple promotions for a product brief. Also, I have an originator (as
orgntr_rsrc_id) stored in the product brief table. The originator's first
and last name is stored in the resource table with rsrc_id as the key.

I have 2 unbound fields on the form that the user maintains to setup the
date range parameters to filter the records that are returned. They are
txtFromDate and txtToDate.

I have tried to accomplish this 2 different ways, one using the product
brief table as a record source and applying a serverfilter and the other
using a stored procedure as a record source, passing the dates as
parameters.
In both cases the product promotions are handled with a subform and that
peice works.

Method 1:
I use the product brief table as the forms record source. The user enters
the begin and end dates and click a "search" button which runs the
following
code to apply a serverfilter.

Private Sub Command213_Click()
If IsNull(Me.txtFromDate) Then
fromdate = #1/1/1900#
Else
fromdate = Me.txtFromDate.Value
End If

If IsNull(Me.txtToDate) Then
todate = #1/1/1900#
Else
todate = Me.txtToDate.Value
End If

Me.ServerFilter = "rtl_lnch_dt between '" & CStr(fromdate) & "' and '" _
& CStr(todate) & "' Or smpl_prgms_dt between '" & CStr(fromdate) & "' and
'" _
& CStr(todate) & "'"
Me.Refresh
End Sub

This code is ok, and technically accomplishes what the user wants EXCEPT
they want the resource name, rather than Id, to appear. I have tried the
following select in the control source of the Originator text box but I
get
#Name returned. Spellings are correct--if I copy this select over to the
server (MS SQL Server Management Studio), remove the parenthesis and
change
the &'s to +'s it runs just fine

=(SELECT isnull([dbo].[rsrc_t].[rsrc_first_nm]
&''&[dbo].[rsrc_t].[rsrc_last_nm],' ') From [dbo].[rsrc_t] inner join
[dbo].[prdct_brief_t] on
[dbo].[rsrc_t].[rsrc_id]=[dbo].[prdct_brief_t].[orgntr_rsrc_id])

To my knowledge, you cannot call directly a Select query this way; you
should either set it up on the OnCurrent event or use a DLookUp() call or
use an intermediate public VBA function like (not tested):


Public Function TestFunction(orgntr_rsrc_id) As String

If (IsNull(orgntr_rsrc_id)) Then
TestFunction = ""
Else
TestFunction= [CurrentProject].[Connection].[Execute]("SELECT TOP 1
isnull([dbo].[rsrc_t].[rsrc_first_nm]
&''&[dbo].[rsrc_t].[rsrc_last_nm],' ') as NM From [dbo].[rsrc_t] WHERE
[dbo].[rsrc_t].[rsrc_id]=" & orgntr_rsrc_id &")("NM")

End if

'''' For debugging:
' Debug.Print orgntr_rsrc_id
' Debug.Print TestFunction

End Function


and you set the record source to be:

=TestFunction([orgntr_rsrc_id])

Maybe I can't do this in an ADP???

Method 2:
The control source for the form is:
exec pd_upd_NPI_p '3/1/2009', '3/31/2009'

where the 2 dates are the parameters for the beginning and ending date
ranges. The stored procedure correctly formats the Originator Name. It
opens with the correct data for the dates hard coded in the record source.
I
am able to modify the records as desired. Seems Perfect! Then I change
the
dates on the form to 10/01/2008 and 10/31/2008 and click my search button
to
perform the following code:
Private Sub cmdRtrvRcrds_Click()
Dim fromdate As Date
Dim todate As Date

If IsNull(Me.txtFromDate) Then
fromdate = #1/1/1900#
Else
fromdate = Me.txtFromDate
End If

If IsNull(Me.txtToDate) Then
todate = #1/1/1900#
Else
todate = Me.txtToDate
End If

Me.RecordSource = "exec pd_upd_NPI_p '" & CStr(fromdate) & "', '" &
CStr(todate) & "'"

This code will then show the correct records for the October date range.
However, when I click on any of the records in the datasheet view, one of
two
things will happen. I may get a message on the first click that says "One
of
your parameters is invalid" with no error number and OK as the only
option. I
click OK and select a different record (or click on the same record again)
and Access will hang-[Not Responding]. I have to alt-ctrl-del to shut it
down.
The other thing that may happen is really the same thing, but I don't get
the parameter message at all--I go immediately to not responding.

I am wondering if the resync command may be the key here. I have no idea
how to use it--I have done some searches, but haven't gotten a warm fuzzy
feeling that I know how to use it. If resync isn't the answer--any other
suggestions would be welcome.

..and yes, it has to stay a "project" because the boss says so.

You don't show the code for your SP and the schema of your table. First,
make sure that you have set the NoCount property to ON at the very beginning
of your SP:

Set NoCount ON

Second, what are you using as the primary key of your brief table? Set the
UniqueTable property to the name of this table and create a SP using this
primary key as its single parameter for returning the value of the *current*
row based on this Id. Next, you set the CommandResync command to the name
of this SP following by a ? as in:

MyResyncSP ?

Finally, your heavy use of the character _ make your code very hard to read.
Your description of your form and of your datasheet is not very clear, too.

 

[Vikki D]

I apologize for the difficulty reading the code. It doesn’t help, but I
totally agree with you. However, I don’t get to be the DBA and set the naming
standards. For this discussion, I will remove the underscores.

By the way, your TestFunction worked like a charm. Thank you. I had the
mistaken impression that functions had to be on the server if using ADP.
This function exists on the server, but I could never come up with the syntax
to call it. I am going ahead with method number 1.

However, I would like to understand more about the resync command used for
method number 2. I did put it in, but Access crashed as soon as I selected a
record.

I will try to be clearer with what I am doing. Please know that my problem
is solved—I’m just trying to learn more, so if this isn’t worthy of your
time, that is fine. Thank you for what you have already done.

Database:

Table number 1:
PrdctBriefT [Product Brief Table]
PrdctBriefID int primary key
PrdctWrkngNm varchar(100)
SmplPrgmDt Datetime
RetlLaunchDt Datetime
RsrcId Int

Table number 2:
PrdctPrmtnT [Product Promotions Table]
PrdctPrmtnID int (primary key)
PrdctPrmtnDscr varchar(30)

Table number 3:
PrdctBriefPrdctPrmtnT [Product Brief/Product Promotion table]
PrdctBriefPrdctPrmtnID in int (primary key)
PrdctBriefID int (foreign key)
PrdctPrmtnID int (foreign key)

Table number 4:
RsrcT [Resources Table]
RsrcID int (primary key)
RsrcFirstNM varchar(50)
RsrcLastNM varchar(50)

Access:

NPI Mass Update (form)
The main purpose of the form is to add records to Product Brief/Product
Promotion table (table 3). This table is an intermediate table between the
Product Brief table (table1) and the Product Promotions Table (table3)
because of the many-to-many relationship between the product briefs and the
promotions.

The users want to inspect and possibly update the promotions for all
products whose sampling data OR retail launch date falls within a specific
range.
I chose a split form because the top part of the form allows ease of update
to the promotions on one record. The bottom half allows them to see all of
the records that are in the date range.

The header of the form contains the two unbound date fields to specifiy the
range.

txtFromDate
txtToDate
cmdRtrvRcrds (command button)


The top of the split form contains:

PrdctWrkngNm (Product Working Name)
PrdctSmplgDt (Product Sampling Date
RtlLaunchDt (Retail Launch Date)
Originator (Originator first & last name selected from RsrcT using RsrcID
A Subform for Product Promotions:
LinkMasterField: PrdctBrief.PrdctBriefID
LinkChildField: PrdctBriefPrdctPrmtnT.PrdctBriefID
RecordSource: PrdctBriefPrdctPrmtnT
Combobox with PrdctBriefPrdctPrmtn.PrdctBriefPrdctPrmtnID stored
and PrdctPrmtnT.PrdctPrmtnDscr displayed.

The bottom half of the form shows the same fields but in datasheet view. It
is to be used to select a new record to update


Record source: exec pdUpdNPIp ‘03/01/2009’, ‘03/31/2009’
(This range is a range that contains valid data and is arbitrary. The date
range is manipulated by VBA code when the cmdRtrvRcrds is clicked. That code
was previously posted. I would prefer to put dates in that will initially
bring up a form that is blank, but that is down the road.)

Unique Table: PrdctBriefT
Resync Command: pd_resync ?

Pd_rsync code: (I may have misunderstood what was needed here)
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
Alter PROCEDURE [dbo].[pd_resync]
@PrdctBriefID int = '1'

AS SELECT distinct dbo.PrdctBriefT.PrdctBriefId
FROM dbo.PrdctBriefT
WHERE (dbo.PrdctBriefT.PrdctBriefId = @PrdctBriefID)


Hopefully this explanation is better. However the problem didn’t change.
If I manipulate the records retrieved by using the date range of 03/01/2009 -
03/31/2009 as specified in the actual exec statement, everything works fine.
If I key a different date range, the form displays, but then clicking on a
different record in the subset on the bottom half of the form causes Access
to crash.

As I said, my problem is solved with Method number 1 from the previous post,
but this drives me crazy.

Again, thanks for your time so far.
Vikki