how to call function from on click event

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

how to call function from on click event

Author

Topic

osupratt
Posting Yak Master

238 Posts

Posted - 2010-12-13 : 12:16:40

I have this code that I believe Alan Browne or someone had posted. I keep
getting the "Compile Error - Argument Not Optional" error message. I am
probably calling this function incorrectly, but am definately not a vb or
access guru. I come to this site as it has helped me immensely in the past.
Here is the code I pasted into one of my modules:

Public Function Duplicate(ByVal strKey As String, ByVal strTable As String, _
ByVal lngID As Long) As Boolean

On Error GoTo Err_Duplicate

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim fld As DAO.Field
Dim varValue As Variant

Set db = CurrentDb
Set rs = db.OpenRecordset(strTable)

With rs
.AddNew
For Each fld In rs.Fields
varValue = DLookup("[" & fld.Name & "]", strTable, "[" & strKey &
"] = " & lngID)
If Not IsNull(varValue) And fld.Name <> strKey Then
fld = varValue
End If
Next
.Update
.Close
End With

Duplicate = True

Exit_Duplicate:
Set fld = Nothing
Set rs = Nothing
Set db = Nothing
Exit Function

Err_Duplicate:
Duplicate = False
Resume Exit_Duplicate

End Function

A little background is that I have a subform that is basically a billing form.
It has item number, billing code, qty, unit price, etc. I want to be able to
copy the existing record and paste it in the next or new record. I tried the
Duplicate on the command button wizard but got the error about INSERT
IDENTITY off. So I was hoping the above code will fix this issue. So I'm
probably calling the above function from the on click event of the command
button incorrectly:

Public Sub cmdCopy_Click()
Call Duplicate
End Sub

Any help would be greatly appreciated as I am struggling with this. Thanks.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-12-14 : 03:58:15

When you go "Call Duplicate"....you're not passing in or receiving out any variables.

Without looking into the internals of the function, it should be "d=Duplicate(a,b,c)" where d is a boolean variable to be the return code from the function, and a,b,c are variables local to cmdCOPY (or a higher precednce procedure) by which you pass data into and get data back from the function.

osupratt
Posting Yak Master

238 Posts

Posted - 2010-12-14 : 10:37:02

Thanks Andrew for your response. After fighting with this for about a day and a half I finally realized that I didn't have my parent/child link from main form to sub-form set. After I did this the command button wizard 'duplicate record' worked. Big ole' dummy. Anyhow, thanks again. I was wondering why microsoft would have this built in function if it didn't work.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources