Duplicate the record in form and subform

Hi Allen Browne,

I try to use your code to duplicate the record in form and subform but I got 
a error message "Error#3265". BTW, my primary key value is not autonumber, 
how to change the code to fit my database? 
Thanks for your expert comments in advance!


0
Utf
12/15/2007 1:53:00 PM
access.formscoding 7493 articles. 0 followers. Follow

9 Replies
1112 Views

Similar Articles

[PageSpeed] 36

Presumably we are talking about this article:
    http://allenbrowne.com/ser-57.html

The "Item not found" error was caused by which line?
Whichever line it is, you have some name Access can't identify, e.g. you 
have not changed the field names to match what's in your form.

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Andy" <Andy@discussions.microsoft.com> wrote in message
news:F433142D-F96C-41EC-AD9F-8408A6D669A4@microsoft.com...
> Hi Allen Browne,
>
> I try to use your code to duplicate the record in form and subform but I 
> got
> a error message "Error#3265". BTW, my primary key value is not autonumber,
> how to change the code to fit my database?
> Thanks for your expert comments in advance! 

0
Allen
12/15/2007 3:16:18 PM
Hi Allen,

Sorry for the typo error on the field name! Have corrected but another error 
message is "error#3058, primary key value can not be null" caused by below 
line :
..update
Have mentioned that my primary key value is not a autonumber, please advise 
how to change the code to fit my database.
Thanks again!

"Allen Browne" wrote:

> Presumably we are talking about this article:
>     http://allenbrowne.com/ser-57.html
> 
> The "Item not found" error was caused by which line?
> Whichever line it is, you have some name Access can't identify, e.g. you 
> have not changed the field names to match what's in your form.
> 
> -- 
> Allen Browne - Microsoft MVP.  Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
> 
> "Andy" <Andy@discussions.microsoft.com> wrote in message
> news:F433142D-F96C-41EC-AD9F-8408A6D669A4@microsoft.com...
> > Hi Allen Browne,
> >
> > I try to use your code to duplicate the record in form and subform but I 
> > got
> > a error message "Error#3265". BTW, my primary key value is not autonumber,
> > how to change the code to fit my database?
> > Thanks for your expert comments in advance! 
> 
> 
0
Utf
12/15/2007 3:47:00 PM
Whatever method you're using to create your PK is going to have to be done 
programmatically in your code i.e.

With Me.RecordsetClone
            .AddNew
                ![PK field] = some value
                !CustomerID = Me.CustomerID
                !EmployeeID = Me.EmployeeID
                !OrderDate = Date
                'etc for other fields.
            .Update

-- 
_________

Sean Bailey


"Andy" wrote:

> Hi Allen,
> 
> Sorry for the typo error on the field name! Have corrected but another error 
> message is "error#3058, primary key value can not be null" caused by below 
> line :
> .update
> Have mentioned that my primary key value is not a autonumber, please advise 
> how to change the code to fit my database.
> Thanks again!
> 
> "Allen Browne" wrote:
> 
> > Presumably we are talking about this article:
> >     http://allenbrowne.com/ser-57.html
> > 
> > The "Item not found" error was caused by which line?
> > Whichever line it is, you have some name Access can't identify, e.g. you 
> > have not changed the field names to match what's in your form.
> > 
> > -- 
> > Allen Browne - Microsoft MVP.  Perth, Western Australia
> > Tips for Access users - http://allenbrowne.com/tips.html
> > Reply to group, rather than allenbrowne at mvps dot org.
> > 
> > "Andy" <Andy@discussions.microsoft.com> wrote in message
> > news:F433142D-F96C-41EC-AD9F-8408A6D669A4@microsoft.com...
> > > Hi Allen Browne,
> > >
> > > I try to use your code to duplicate the record in form and subform but I 
> > > got
> > > a error message "Error#3265". BTW, my primary key value is not autonumber,
> > > how to change the code to fit my database?
> > > Thanks for your expert comments in advance! 
> > 
> > 
0
Utf
12/15/2007 9:21:00 PM
That's right, Andy

If the primary key is not an Autonumber, you must assign something to it.

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Beetle" <Beetle@discussions.microsoft.com> wrote in message
news:0C90F455-E349-40F2-83A9-1E5EAD5AF577@microsoft.com...
> Whatever method you're using to create your PK is going to have to be done
> programmatically in your code i.e.
>
> With Me.RecordsetClone
>            .AddNew
>                ![PK field] = some value
>                !CustomerID = Me.CustomerID
>                !EmployeeID = Me.EmployeeID
>                !OrderDate = Date
>                'etc for other fields.
>            .Update
>
> -- 
> _________
>
> Sean Bailey
>
>
> "Andy" wrote:
>
>> Hi Allen,
>>
>> Sorry for the typo error on the field name! Have corrected but another 
>> error
>> message is "error#3058, primary key value can not be null" caused by 
>> below
>> line :
>> .update
>> Have mentioned that my primary key value is not a autonumber, please 
>> advise
>> how to change the code to fit my database.
>> Thanks again!
>>
>> "Allen Browne" wrote:
>>
>> > Presumably we are talking about this article:
>> >     http://allenbrowne.com/ser-57.html
>> >
>> > The "Item not found" error was caused by which line?
>> > Whichever line it is, you have some name Access can't identify, e.g. 
>> > you
>> > have not changed the field names to match what's in your form.
>> >
>> > "Andy" <Andy@discussions.microsoft.com> wrote in message
>> > news:F433142D-F96C-41EC-AD9F-8408A6D669A4@microsoft.com...
>> > > Hi Allen Browne,
>> > >
>> > > I try to use your code to duplicate the record in form and subform 
>> > > but I
>> > > got
>> > > a error message "Error#3265". BTW, my primary key value is not 
>> > > autonumber,
>> > > how to change the code to fit my database?
>> > > Thanks for your expert comments in advance! 

0
Allen
12/16/2007 2:31:40 AM
Thanks, Beetle & Allen,

Have changed the code as below but got an error message "Error#3061, 
parameter not much enough, expected is 1" caused by below line :

DBEngine(0)(0).Execute strSql, dbFailOnError

Appreciate your expert commnts! Thanks in advance!

Here is the code :
Private Sub Command36_Click()

'On Error GoTo Err_Handler
    'Purpose:   Duplicate the main form record and related records in the 
subform.
    Dim strSql As String    'SQL statement.
    Dim lngID As String       'Primary key value of the new record.
    
    'Save and edits first
    If Me.Dirty Then
        Me.Dirty = False
    End If
    
    'Make sure there is a record to duplicate.
    If Me.NewRecord Then
        MsgBox "Select the record to duplicate."
    Else
        'Duplicate the main record: add to form's clone.
        With Me.RecordsetClone
            .AddNew
                ![QUOTATION NO] = InputBox("Please enter the quotation 
number :", "Hello Kitty")
                ![COMPANY NO] = Me.[COMPANY NO]
                ![CUSTOMER NO] = Me.[CUSTOMER NO]
                ![LOCATION NO] = Me.[LOCATION NO]
                ![QUOT DATE] = DATE
                ![EFFECTIVE DATE] = Me.[EFFECTIVE DATE]
                ![PAYMENT TERMS] = Me.[PAYMENT TERMS]
                ![DELIVERY TERMS] = Me.[DELIVERY TERMS]
                ![ITEM HEADER] = Me.[ITEM HEADER]
                'etc for other fields.
            .Update
            
            'Save the primary key value, to use as the foreign key for the 
related records.
            .Bookmark = .LastModified
            
            lngID = ![QUOTATION NO]
            
            'Duplicate the related records: append query.
            If Me.[QUOTATION TABLE DETAILS 
subform].Form.RecordsetClone.RecordCount > 0 Then
                strSql = "INSERT INTO [QUOTATION TABLE DETAILS] ( [QUOTATION 
NO], [ITEM NO], [ITEM CONTENT], [QTY], [UNIT], [UNIT PRICE] ) " & _
                    "SELECT " & lngID & " As NewID, [ITEM NO], [ITEM 
CONTENT], [QTY], [UNIT], [UNIT PRICE] " & _
                    "FROM [QUOTATION TABLE DETAILS] WHERE [QUOTATION NO] = " 
& Me.[QUOTATION NO] & ";"
                DBEngine(0)(0).Execute strSql, dbFailOnError
            Else
                MsgBox "Main record duplicated, but there were no related 
records."
            End If
            
            'Display the new duplicate.
            Me.Bookmark = .LastModified
        End With
    End If

Exit_Handler:
    Exit Sub

Err_Handler:
    MsgBox "Error " & Err.NUMBER & " - " & Err.Description, , "cmdDupe_Click"
    Resume Exit_Handler
    
End Sub



"Allen Browne" wrote:

> That's right, Andy
> 
> If the primary key is not an Autonumber, you must assign something to it.
> 
> -- 
> Allen Browne - Microsoft MVP.  Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
> 
> "Beetle" <Beetle@discussions.microsoft.com> wrote in message
> news:0C90F455-E349-40F2-83A9-1E5EAD5AF577@microsoft.com...
> > Whatever method you're using to create your PK is going to have to be done
> > programmatically in your code i.e.
> >
> > With Me.RecordsetClone
> >            .AddNew
> >                ![PK field] = some value
> >                !CustomerID = Me.CustomerID
> >                !EmployeeID = Me.EmployeeID
> >                !OrderDate = Date
> >                'etc for other fields.
> >            .Update
> >
> > -- 
> > _________
> >
> > Sean Bailey
> >
> >
> > "Andy" wrote:
> >
> >> Hi Allen,
> >>
> >> Sorry for the typo error on the field name! Have corrected but another 
> >> error
> >> message is "error#3058, primary key value can not be null" caused by 
> >> below
> >> line :
> >> .update
> >> Have mentioned that my primary key value is not a autonumber, please 
> >> advise
> >> how to change the code to fit my database.
> >> Thanks again!
> >>
> >> "Allen Browne" wrote:
> >>
> >> > Presumably we are talking about this article:
> >> >     http://allenbrowne.com/ser-57.html
> >> >
> >> > The "Item not found" error was caused by which line?
> >> > Whichever line it is, you have some name Access can't identify, e.g. 
> >> > you
> >> > have not changed the field names to match what's in your form.
> >> >
> >> > "Andy" <Andy@discussions.microsoft.com> wrote in message
> >> > news:F433142D-F96C-41EC-AD9F-8408A6D669A4@microsoft.com...
> >> > > Hi Allen Browne,
> >> > >
> >> > > I try to use your code to duplicate the record in form and subform 
> >> > > but I
> >> > > got
> >> > > a error message "Error#3265". BTW, my primary key value is not 
> >> > > autonumber,
> >> > > how to change the code to fit my database?
> >> > > Thanks for your expert comments in advance! 
> 
> 
0
Utf
12/16/2007 9:31:00 AM
The request for a parameter means you have a name Access doesn't recognise. 
So some name in the SQL statement is no correct.

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Andy" <Andy@discussions.microsoft.com> wrote in message
news:0555F0B6-2296-45D1-BFDB-C8EBBE414493@microsoft.com...
> Thanks, Beetle & Allen,
>
> Have changed the code as below but got an error message "Error#3061,
> parameter not much enough, expected is 1" caused by below line :
>
> DBEngine(0)(0).Execute strSql, dbFailOnError
>
> Appreciate your expert commnts! Thanks in advance!

0
Allen
12/16/2007 10:48:05 AM
Allen,

Have checked the names in the SQL statement are correct, what's the problem?
BTW, any change in the SQL statement if the PK value is string?

Here is my code, please check, thanks sooooooooooo much!
Private Sub Command36_Click()

'On Error GoTo Err_Handler
    'Purpose:   Duplicate the main form record and related records in the 
subform.
    Dim strSql As String    'SQL statement.
    Dim lngID As String       'Primary key value of the new record.
    
    'Save and edits first
    If Me.Dirty Then
        Me.Dirty = False
    End If
    
    'Make sure there is a record to duplicate.
    If Me.NewRecord Then
        MsgBox "Select the record to duplicate."
    Else
        'Duplicate the main record: add to form's clone.
        With Me.RecordsetClone
            .AddNew
                ![QUOTATION NO] = InputBox("Please enter the quotation 
number :", "Hello Kitty")
                ![COMPANY NO] = Me.[COMPANY NO]
                ![CUSTOMER NO] = Me.[CUSTOMER NO]
                ![LOCATION NO] = Me.[LOCATION NO]
                ![QUOT DATE] = DATE
                ![EFFECTIVE DATE] = Me.[EFFECTIVE DATE]
                ![PAYMENT TERMS] = Me.[PAYMENT TERMS]
                ![DELIVERY TERMS] = Me.[DELIVERY TERMS]
                ![ITEM HEADER] = Me.[ITEM HEADER]
                'etc for other fields.
            .Update
            
            'Save the primary key value, to use as the foreign key for the 
related records.
            .Bookmark = .LastModified
            
            lngID = ![QUOTATION NO]
            
            'Duplicate the related records: append query.
            If Me.[QUOTATION TABLE DETAILS 
subform].Form.RecordsetClone.RecordCount > 0 Then
                strSql = "INSERT INTO [QUOTATION TABLE DETAILS] ( [QUOTATION 
NO], [ITEM NO], [ITEM CONTENT], [QTY], [UNIT], [UNIT PRICE] ) " & _
                    "SELECT " & lngID & " As NewID, [ITEM NO], [ITEM 
CONTENT], [QTY], [UNIT], [UNIT PRICE] " & _
                    "FROM [QUOTATION TABLE DETAILS] WHERE [QUOTATION NO] = " 
& Me.[QUOTATION NO] & ";"
                DBEngine(0)(0).Execute strSql, dbFailOnError
            Else
                MsgBox "Main record duplicated, but there were no related 
records."
            End If
            
            'Display the new duplicate.
            Me.Bookmark = .LastModified
        End With
    End If

Exit_Handler:
    Exit Sub

Err_Handler:
    MsgBox "Error " & Err.NUMBER & " - " & Err.Description, , "cmdDupe_Click"
    Resume Exit_Handler
    
End Sub

"Allen Browne" wrote:

> The request for a parameter means you have a name Access doesn't recognise. 
> So some name in the SQL statement is no correct.
> 
> -- 
> Allen Browne - Microsoft MVP.  Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
> 
> "Andy" <Andy@discussions.microsoft.com> wrote in message
> news:0555F0B6-2296-45D1-BFDB-C8EBBE414493@microsoft.com...
> > Thanks, Beetle & Allen,
> >
> > Have changed the code as below but got an error message "Error#3061,
> > parameter not much enough, expected is 1" caused by below line :
> >
> > DBEngine(0)(0).Execute strSql, dbFailOnError
> >
> > Appreciate your expert commnts! Thanks in advance!
> 
> 
0
Utf
12/16/2007 2:12:00 PM
If you foreign key is a string, it must be enclosed in quotes. For an 
explanation of how to do that, see:
    Quotation marks within quotes
at:
    http://allenbrowne.com/casu-17.html

Further up the routine, the primary key value is saved as a Long. That may 
not be right either if this is Text.

To help you with the debugging, insert the line:
    Debug.Print strSql
just above the Execute. When it fails, you can then open the Immediate 
Window (Ctrl+G), copy the statement to clipboard, and paste into SQL View in 
a query. You can then sort it out, or compare it to an example query that 
does work.

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Andy" <Andy@discussions.microsoft.com> wrote in message
news:92C8331D-317A-4536-A566-13BC057CDCB6@microsoft.com...
> Allen,
>
> Have checked the names in the SQL statement are correct, what's the 
> problem?
> BTW, any change in the SQL statement if the PK value is string?
>
> Here is my code, please check, thanks sooooooooooo much!
> Private Sub Command36_Click()
>
> 'On Error GoTo Err_Handler
>    'Purpose:   Duplicate the main form record and related records in the
> subform.
>    Dim strSql As String    'SQL statement.
>    Dim lngID As String       'Primary key value of the new record.
>
>    'Save and edits first
>    If Me.Dirty Then
>        Me.Dirty = False
>    End If
>
>    'Make sure there is a record to duplicate.
>    If Me.NewRecord Then
>        MsgBox "Select the record to duplicate."
>    Else
>        'Duplicate the main record: add to form's clone.
>        With Me.RecordsetClone
>            .AddNew
>                ![QUOTATION NO] = InputBox("Please enter the quotation
> number :", "Hello Kitty")
>                ![COMPANY NO] = Me.[COMPANY NO]
>                ![CUSTOMER NO] = Me.[CUSTOMER NO]
>                ![LOCATION NO] = Me.[LOCATION NO]
>                ![QUOT DATE] = DATE
>                ![EFFECTIVE DATE] = Me.[EFFECTIVE DATE]
>                ![PAYMENT TERMS] = Me.[PAYMENT TERMS]
>                ![DELIVERY TERMS] = Me.[DELIVERY TERMS]
>                ![ITEM HEADER] = Me.[ITEM HEADER]
>                'etc for other fields.
>            .Update
>
>            'Save the primary key value, to use as the foreign key for the
> related records.
>            .Bookmark = .LastModified
>
>            lngID = ![QUOTATION NO]
>
>            'Duplicate the related records: append query.
>            If Me.[QUOTATION TABLE DETAILS
> subform].Form.RecordsetClone.RecordCount > 0 Then
>                strSql = "INSERT INTO [QUOTATION TABLE DETAILS] ( 
> [QUOTATION
> NO], [ITEM NO], [ITEM CONTENT], [QTY], [UNIT], [UNIT PRICE] ) " & _
>                    "SELECT " & lngID & " As NewID, [ITEM NO], [ITEM
> CONTENT], [QTY], [UNIT], [UNIT PRICE] " & _
>                    "FROM [QUOTATION TABLE DETAILS] WHERE [QUOTATION NO] = 
> "
> & Me.[QUOTATION NO] & ";"
>                DBEngine(0)(0).Execute strSql, dbFailOnError
>            Else
>                MsgBox "Main record duplicated, but there were no related
> records."
>            End If
>
>            'Display the new duplicate.
>            Me.Bookmark = .LastModified
>        End With
>    End If
>
> Exit_Handler:
>    Exit Sub
>
> Err_Handler:
>    MsgBox "Error " & Err.NUMBER & " - " & Err.Description, , 
> "cmdDupe_Click"
>    Resume Exit_Handler
>
> End Sub
>
> "Allen Browne" wrote:
>
>> The request for a parameter means you have a name Access doesn't 
>> recognise.
>> So some name in the SQL statement is no correct.
>>
>> -- 
>> Allen Browne - Microsoft MVP.  Perth, Western Australia
>> Tips for Access users - http://allenbrowne.com/tips.html
>> Reply to group, rather than allenbrowne at mvps dot org.
>>
>> "Andy" <Andy@discussions.microsoft.com> wrote in message
>> news:0555F0B6-2296-45D1-BFDB-C8EBBE414493@microsoft.com...
>> > Thanks, Beetle & Allen,
>> >
>> > Have changed the code as below but got an error message "Error#3061,
>> > parameter not much enough, expected is 1" caused by below line :
>> >
>> > DBEngine(0)(0).Execute strSql, dbFailOnError
>> >
>> > Appreciate your expert commnts! Thanks in advance!
>>
>> 

0
Allen
12/17/2007 10:10:15 AM
Dear Allen,

It works great now. Thanks very much for your expert comments!!!

"Allen Browne" wrote:

> If you foreign key is a string, it must be enclosed in quotes. For an 
> explanation of how to do that, see:
>     Quotation marks within quotes
> at:
>     http://allenbrowne.com/casu-17.html
> 
> Further up the routine, the primary key value is saved as a Long. That may 
> not be right either if this is Text.
> 
> To help you with the debugging, insert the line:
>     Debug.Print strSql
> just above the Execute. When it fails, you can then open the Immediate 
> Window (Ctrl+G), copy the statement to clipboard, and paste into SQL View in 
> a query. You can then sort it out, or compare it to an example query that 
> does work.
> 
> -- 
> Allen Browne - Microsoft MVP.  Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
> 
> "Andy" <Andy@discussions.microsoft.com> wrote in message
> news:92C8331D-317A-4536-A566-13BC057CDCB6@microsoft.com...
> > Allen,
> >
> > Have checked the names in the SQL statement are correct, what's the 
> > problem?
> > BTW, any change in the SQL statement if the PK value is string?
> >
> > Here is my code, please check, thanks sooooooooooo much!
> > Private Sub Command36_Click()
> >
> > 'On Error GoTo Err_Handler
> >    'Purpose:   Duplicate the main form record and related records in the
> > subform.
> >    Dim strSql As String    'SQL statement.
> >    Dim lngID As String       'Primary key value of the new record.
> >
> >    'Save and edits first
> >    If Me.Dirty Then
> >        Me.Dirty = False
> >    End If
> >
> >    'Make sure there is a record to duplicate.
> >    If Me.NewRecord Then
> >        MsgBox "Select the record to duplicate."
> >    Else
> >        'Duplicate the main record: add to form's clone.
> >        With Me.RecordsetClone
> >            .AddNew
> >                ![QUOTATION NO] = InputBox("Please enter the quotation
> > number :", "Hello Kitty")
> >                ![COMPANY NO] = Me.[COMPANY NO]
> >                ![CUSTOMER NO] = Me.[CUSTOMER NO]
> >                ![LOCATION NO] = Me.[LOCATION NO]
> >                ![QUOT DATE] = DATE
> >                ![EFFECTIVE DATE] = Me.[EFFECTIVE DATE]
> >                ![PAYMENT TERMS] = Me.[PAYMENT TERMS]
> >                ![DELIVERY TERMS] = Me.[DELIVERY TERMS]
> >                ![ITEM HEADER] = Me.[ITEM HEADER]
> >                'etc for other fields.
> >            .Update
> >
> >            'Save the primary key value, to use as the foreign key for the
> > related records.
> >            .Bookmark = .LastModified
> >
> >            lngID = ![QUOTATION NO]
> >
> >            'Duplicate the related records: append query.
> >            If Me.[QUOTATION TABLE DETAILS
> > subform].Form.RecordsetClone.RecordCount > 0 Then
> >                strSql = "INSERT INTO [QUOTATION TABLE DETAILS] ( 
> > [QUOTATION
> > NO], [ITEM NO], [ITEM CONTENT], [QTY], [UNIT], [UNIT PRICE] ) " & _
> >                    "SELECT " & lngID & " As NewID, [ITEM NO], [ITEM
> > CONTENT], [QTY], [UNIT], [UNIT PRICE] " & _
> >                    "FROM [QUOTATION TABLE DETAILS] WHERE [QUOTATION NO] = 
> > "
> > & Me.[QUOTATION NO] & ";"
> >                DBEngine(0)(0).Execute strSql, dbFailOnError
> >            Else
> >                MsgBox "Main record duplicated, but there were no related
> > records."
> >            End If
> >
> >            'Display the new duplicate.
> >            Me.Bookmark = .LastModified
> >        End With
> >    End If
> >
> > Exit_Handler:
> >    Exit Sub
> >
> > Err_Handler:
> >    MsgBox "Error " & Err.NUMBER & " - " & Err.Description, , 
> > "cmdDupe_Click"
> >    Resume Exit_Handler
> >
> > End Sub
> >
> > "Allen Browne" wrote:
> >
> >> The request for a parameter means you have a name Access doesn't 
> >> recognise.
> >> So some name in the SQL statement is no correct.
> >>
> >> -- 
> >> Allen Browne - Microsoft MVP.  Perth, Western Australia
> >> Tips for Access users - http://allenbrowne.com/tips.html
> >> Reply to group, rather than allenbrowne at mvps dot org.
> >>
> >> "Andy" <Andy@discussions.microsoft.com> wrote in message
> >> news:0555F0B6-2296-45D1-BFDB-C8EBBE414493@microsoft.com...
> >> > Thanks, Beetle & Allen,
> >> >
> >> > Have changed the code as below but got an error message "Error#3061,
> >> > parameter not much enough, expected is 1" caused by below line :
> >> >
> >> > DBEngine(0)(0).Execute strSql, dbFailOnError
> >> >
> >> > Appreciate your expert commnts! Thanks in advance!
> >>
> >> 
> 
> 
0
Utf
12/17/2007 1:09:01 PM
Reply:

Similar Artilces:

Additional lookup for contacts on opportunity form
Hi, I try to customise CRM 3.0 without any training - therefore a question for which I need some help. On the opportunity form exists a look-up (potentional customer) for contacts/accounts. I'd like to add an aditional one to the form (to replace the decision maker). I tryed to create a new field on the opportunity entity but there's no field-type "look-up". It's also not possible to create a new relationship to the contact entity (both system entity's). Does anybody of you know how I can do this? (if yes - please explain it more detailed...). Thanks Joerg Look...

Forms 07-27-07
Hi, A VB Code is used that has the following line: Me.Visible = False (Me refers to the forms names frmBirthsearch) After this line a second form (form2) is opned based on a query that reads two dates from the frmBirthsearch using the line code: DoCmd.OpenForm "frmBirthdates", acViewNormal at this point frmbearthsearh is hidden. What command shold I add after this in order to close the frmbirthsearch? ...

Sql to find record in a hierarchical chain
Hi, lets say I have a table with 2 fields: myTable Field 1 - ref Field 2 - parentRef Now in the structure there could be numerous chains until you get to the top of the hieratchy (where the ref field = parentRef field). ie ref parentRef 111 222 222 333 333 444 444 444 so in this 444 is top of the chain (there will be many other records as well that are nothing to with 444 and are part of their own hierarchy). So how do I easily relate 111 to 444. Ie how do i find the ultimate top parent for a given 'ref' field. Hope anyone can give me some pointers ...

Query to hide duplicate records
I recall that this used to easy in previous versions (Unique values only ??), but in 2007, I can't get this to work at all. I have a table with our companie's job numbers in it. The job numbers show up multiple times because of different phases of the project: ProjNum ProjDescription 3077 Univ. of Vermont/UC/LEED 3077 Univ. of Vermont/University Commons: Building Fee 3077 Univ. of Vermont/University Commons: Excess Professiona... I need to jut have a single listing of each project number, otherwise, I get repeated records in the query that looks at this information (which ...

Excel ask duplicate NAMES when duplicate a worksheets
I have added a NAME called "Above" where point to the cell just above the current cell. The formula is "=INDIRECT("R[-1]C",)" In some workbook, when I duplicate a worksheets, this name will remain silent and work ok. But in some workbooks, when I first duplicate a worksheets, the same name ABOVE will be duplicate and a new local name (belongs to that new worksheet) will be created. If I further duplicate that new worksheets in to a new worksheets, the third worksheets will be warned that a dupicate NAME is existed and ask whether refer to another name or use a ne...

Error 2455 Closing Access 2007 database with form open
I have a form with a subform that is requeried when you select a new key for the main form from a combo box. Everything works fine - usually. But sometimes if you have the form open when you close the database down you get the following error message (twice) in a pop up. You say OK (twice) and the database closes OK "2455 you entered an expression that has an invalid reference to the property form/report" If I close the form before the database I never get the error. If I do not touch the form before you close the database I don't get the error. If I update a field by t...

Keeping a table in a form editable and checking that fields are filled in before allowing a save
Firstly apologies if this is the incorrect forum but I was looking for a general word forum and could not find one. Please point me to one if one exists. I am trying to create a form where I want to specify what items need to be filled in. (Review minutes from design reviews). I want to make certain fields mandatory like the date, attendees and check list used and want to block saving of the document with a warning until they are filled in. Is there a way of doing this? Also as a part of the review actions are filled in to a table. depending on how many actions there are the table...

Duplicates
In the following folders: 1. Sent 2. Inbox 3. Calendar is there a way to eliminate duplicates without going one-by-one & deleting? Thanks for any help.. T Jones ...

Subform question 04-09-10
I have a form (Form1) that contains a subform (Subform1). Within this subform I have a combo box which, depending on what is chosen, pops up another form (Popup1)for additional information. I need this additional information in the form that pops up to be 'linked' with the subform. The problem I am running into is that when the user enters information in Popup1, the table has not been populated witht he data that is in the subform so there is no record to 'link' to. What is the best way to force te esubform to pass its information to the table? Thanks i...

obtaining data in text form from a table
Hi all, I like to be able to obtain the dates in a text format from the table below. ie Test2 8-Feb Test5 4-Feb,8-Feb Test6 4-Feb,5-Feb, 9-Feb Do I need to do this by macros and if so, any help would be appreciated. Table Care Recipient Surname 4-Feb 5-Feb 8-Feb 9-Feb Test1 Test2 8-Feb Test3 Test4 Test5 4-Feb 8-Feb Test6 4-Feb 5-Feb 9-Feb Vlookup should do what you want, as in: =vlookup(a2,Table,2,false) Adjust the ranges t...

Excel for Customer History Records
Hello, can anyone direct me to an existing template that can be used to track customer enquiries and feedback. It needs an efficient way of recording follow up & response conversations. In a standard workbook layout if you type loads of text into a column (say 'follow up' for instance), it pushes all the rows so far down the spreadsheet it quickly becomes unwieldy. If anyone can help I could e mail an example of what we're using now (awful) and after having a chuckle at our expense maybe you can suggest something more appropriate. We're running 2003. Many thanks --...

forms and column lengths
Is there a way to have excel do an auto "carriage return" to the next row when you have reached the specified maximum number of characters in the row above?? Hi there's n o bulit-in feature for this -- Regards Frank Kabel Frankfurt, Germany "Blair" <Blair@discussions.microsoft.com> schrieb im Newsbeitrag news:C1D2CAAD-C4E9-492A-ADF4-CBDB659514A3@microsoft.com... > Is there a way to have excel do an auto "carriage return" to the next row > when you have reached the specified maximum number of characters in the > row > above?? ...

Enable/Disable a Form Control Based on Security Group Permissions
How do I enable or disable a control in a form based on a user’s security group membership? For example: If I have a checkbox on a form (call it box1), I want box1 to be enabled if the user who opened Access is a member of a security group called “Breaker Test Admin.” For members of any other group (except of course “Admins”), box1 should be disabled. Thank you, for your help! On Mon, 02 Jul 2007 18:57:13 GMT, "BenS" <u35527@uwe> wrote: >How do I enable or disable a control in a form based on a user�s security >group membership? For example: If I have a checkbox on a ...

Trying to Create a pop-up form
Due to size limitations on my main form, I am trying to create a form that, when I select a button, it pops up like a dialog box with the main form, Frm_Escheat, in the background I've created a qry which is the record source on my pop-up form. In the criteria of the query is: [Forms]![Frm_Escheat]![statecode] where [statecode] is the field from the Master Form. So, my "pop-up" form is activated when I select the button, but my problem is that it just goes to the pop-up form without the main form showing in the background. Thanks for your help. It would probably hel...

EXTRACTING UNIQUE RECORD BASED ON CONDITION
This is a multi-part message in MIME format. ------=_NextPart_000_0012_01C781BF.08FB92F0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello everyone! I would like to extract unique records based on a condition. For = example, how to extract unique record from column 'B' when column 'A' = has "AP" or any other desired condition. The data is as follows: A B =20 MI 70056542=20 MI 70056543=20 AP PATRICK CUDAHY INCORPORATED=20 AP PATRICK CUDAHY INCORPORATED=20 AP SUGAR CREEK ...

Auto Fill a Textbox in Form
What is the simplest way to auto fill a textbox (in a form) with the maximum values of more than one field in the last record entered, in the same table? Can the =DMax function be used for this? I have tried =DMax(“[field_1]”, “[field_2]”, “[field_3]” , “table_1”)+1, but this doesn’t work. Is something like this possible with two or more feilds with DMax? Thanks -- Message posted via http://www.accessmonster.com See MinOfList() and MaxOfList() here: http://allenbrowne.com/func-09.html -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allen...

user forms #5
Hello, I have created a user form (click on New Employee). How do I get the information entered in to this form to appear in the correct columns in the Payroll data sheet? And how do I get the combo boxes to display the drop down information from the sheet called input. Regards Runner77 -- Runner77 ------------------------------------------------------------------------ Runner77's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30388 View this thread: http://www.excelforum.com/showthread.php?threadid=500502 ...

Mail merge duplications in Outlook
I am attempting an e-mail merge for a letter written in Word with my Excel list as I had done in previous versions of Word. When I do this, multiple copies of the the e-mail are sent to the e-mail recipients and the <<name>> field transposes different names from other records. The sent items appears correctly in the Outlook sent folder? My Outlook is setup to send/receive through a Pop3 Account. Has anybody encountered a similar problem or does anybody know of a fix? Thanks in adavance for your help! Eric ...

excel 2007
I have data that looks something like this account#, invoice amount 1, 50 1, 70 1, 80 2, 10 2, 50 2, 52 2, 50 3, 10 4, 30 I need a way to combine all the invoice totals for each account so I have results like this 1, 200 2, 162 3, 10 4, 30 Any suggestions on how to go about doing this? I thought I had done something similar to this with filters before in Excel 2003, but I can't figure out how I did it. Thanks! You can use subtotal or a pivot table in the pivot put the account numbers in the row field and the invoice amount in the data, in subtotal use at each change in account numb...

Adding GAL users into a custom form
Hi, I have created a custom form, and am required to add a series of approvers. What I am trying to achieve is: * Add users from my GAL into a text field, so that when the form is sent, they do not get initially CC'ed the form. Is this achievable, and if so, how do I do it (if you could help by including any appropriate code, that would be great). Regards, Rick ...

2007 duplicate accounts?
In previous versions of Money (2005 & 2006) there was a duplicate account problem. I described it previously here: http://groups.google.com/group/microsoft.public.money/msg/61a41a7b3e0efcaf http://groups.google.com/group/microsoft.public.money/msg/b9b1de03d4a5d911 I've searched the group for comments from current M07 users about whether this problem still exists, but haven't found it. If I missed it, my apologies. Would you kindly send me a pointer? If I didn't miss it, has anyone who's using M07 seen this problem? TIA - Mark I'm not using 2007 but I think what...

subform not refreshing
Hi there, I have a main form with a subform embedded to be able to scroll through a list of records. There is no linking field between the two. The subform displays all the records properly. One of the fields in the subform is a checkbox to 'close' the record indicating that the info is no longer current and shouldn't be displayed. This works fine. I should also mention that the subform is based on a query. I have a button on the main form which opens another modal form based on the same query as the subform that will let me uncheck the checkbox in case the record was 'clo...

Duplicate Inbox
Hi! I formatted my hardrive and installed Office 2003 again. Copied the pst file. Every thing seems to be working fine, but I see two OUTLOOK TODAY in my All Mail Folder. They are exactly the same, and both of them expand at the time of receive emails. How do I remove the other one. One item has OUTLOOK TODAY icon, however the one has icon same as when you attach any other PST file. Please help If you right-click the root folder of the extra .PST file and select Close from the context menu, does it close? If not, can you remove it from your profile using File | Data File Management? -- ...

User Form Formula ?
Hi On a user form I have 2 Text Boxes in which to enter cash figures Excluding TAX - on the worksheet these 2 figures enter columns E & G - in column K I have the formula =IF(SUM(E3+G3)=0"", SUM(E3+G3)*17.5% - If I create a new Text Box on the user form could it generate the Tax as the formula above and then on clicking Add Iformation Button it would enter column K thus doing away with the formula in column K on the worksheet which is sometimes altered by mistake. Any help much appreciated Cheers ---- Mully Yes, you could add TextBox3.Text = Format(CStr((Val(Tex...

Help with Highlighting all duplicates in a row
I am trying to highlight duplicates in a row and am using the explanation: Highlighting Duplicate Entries Our first task is to highlight the cells in Range1 that are duplicates. We use Excel's Conditional Formatting tool to accomplish this. First, highlight the entire Range1. Then, select the Conditional Formatting tool from the Format menu: Format->Conditional Formatting. Change the "Cell Value Is" option to "Formula Is" and enter the following formula in the formula text box: =IF(COUNTIF(Range1, A5)>1,TRUE,FALSE) Where A5 is the first cell in Range1....