I have 2 tables.
Table 1: machine
date machine_num workweek
9/7/2004 1 37
9/7/2004 2 37
9/7/2004 3 37

Table 2 : winder
winder_num machine_num
m1_w1 1
m1_w2 1
m1_w3 1
m1_w4 1
m2_w1 2
m2_w2 2
m2_w3 2
m2_w4 2
m3_w1 3
m3_w2 3
m3_w3 3
m3_w4 3

Both tables related with 1 to many relationship.

I created a main form(machine) subform(winder) each machine will have 4 winders. All the records are being displayed in the forms. I have a date( ) and workweek field in main form.
When the user go to a particular record example machine :1 and winder : 1 and click Print, labels will be printed for that particular records. i want to write an event procedure OnClick,where when the print button clicked i want to store the current machine_num,Date,workweek,winder_num in a table and at the same time every time event occur a sequence number(incremented by 1) will be stored in the same table. This will create a unique id for each records .

Machine_num winder_num date workweek seq_num
1 w1m1 9/7/2004 37 0001

my question is

1)how do I write the event procedure and create the sequential num field?

Really hope any1 out there can help me with this…..thanks in advance



tasha

__________________
tasha

I have a question to know exactly what you need.
Is the seq_num a calculation of how many times a label record in table 2: winder is printed. And mayb3 sequencially record it.

Or maybe you just what a sequence number of every print job made.

John

thanks John....actually i need a sequence number of every print job made. this is because the current system doesnt record all the printing job and the data of it. this causes having wrong labels for particular products. if i have a sequence number for every print job i can trace each products that has been produced in every particular machine and winder.
for an example if winder1 in machine1 produced a reel, the user will go to the particular record in the database and print the labels for that particular reel.so i'll have a record of that reel as
machine winder date workweek seq_num
1 m1_w1 9/10/04 37 0001

hope you understand what exactly i need..really thanks for your reply.


tasha

Tasha,
If this is the case, correct if i don't understand it right.
You first need to create a field named seq_num in Table 2: winder,
this field should have a data type of integer with format: 0000.
Then need to create a Print Button to your form. Then this print
button should have an event function or code. This function will be
under your Modules.

Machine_num winder_num date workweek seq_num printbtn
1 w1m1 9/7/2004 37 0001 X

Given the example above and assuming X is a button.
Your code will be like this:

Public Function Print_OnClick()
On Error GoTo Print_OnClick_Err

    Dim rstUpdRec As New ADODB.Recordset
    Dim sqlUpdRec As String
    Dim intNewseq_num As Integer

    Dim strMachine_num
    Dim strwinder_num

    intseq_num = Screen.ActiveForm.seq_num.Value
    strMachine_num = Screen.ActiveForm.Machine_num.Value
    strwinder_num = Screen.ActiveForm.winder_num.Value

    If IsNull(strwinder_num) Then
        DoCmd.CancelEvent
    ElseIf IsNull(strMachine_num) Then
        DoCmd.CancelEvent
    Else

        '-------------------------------------------------'
        ' Create a new seq_num record FROM winder table '
        '-------------------------------------------------'
        sqlUpdRec = "SELECT Max(seq_num)+1 As newseq_num FROM winder"
        rstUpdRec.Open sqlUpdRec, CurrentProject.Connection, adOpenKeyset, adLockPessimistic

        If rstUpdRec.EOF Then
            rstUpdRec.Close
            Set rstUpdRec = Nothing

        Else
            intNewseq_num = rstUpdRec("newseq_num").Value
            rstUpdRec.Close
            Set rstUpdRec = Nothing
        End If

        '-------------------------------------------------'
        ' Update a new seq_num record FROM winder table '
        '-------------------------------------------------'
        sqlUpdRec = "SELECT winder_num, machine_num, seq_num FROM winder WHERE seq_num = " & intNewseq_num
        rstUpdRec.Open sqlUpdRec, CurrentProject.Connection, adOpenKeyset, adLockPessimistic

        If rstUpdRec.EOF Then
            rstUpdRec.AddNew
            rstUpdRec("Machine_num").Value = strMachine_num
            rstUpdRec("winder_num").Value = strwinder_num
            rstUpdRec("seq_num").Value = intNewseq_num
            rstUpdRec.Update
            rstUpdRec.Close
            Set rstUpdRec = Nothing

        Else
            rstUpdRec.Close
            Set rstUpdRec = Nothing
        End If

    End If

Print_OnClick_Exit:
    Exit Function

Print_OnClick_Err:
    MsgBox Err.Description
    Resume Print_OnClick_Exit

End Function

This function should be under On Click - Event tab of the print button properties as:

  = Print_OnClick()

I HOPE THIS HELPS YOU.
john

hi John, thanks alot for your guidance...sorry for the late reply.i was on medical leave. i tried the code today and i am getting compile error: method or data member not found. Public Function Print_Onclick()is being highlighted. i put the code on my functions module and set my On Click Event tab as =Print_OnClick.why is that i am still getting an error. is that any steps that i left out? really thanks for your help..


tasha

i realised my mistakes...i just copy paste your code(with some changes) without even understanding it..can you please explain to me how does your code work?

sqlUpdRec = "SELECT Max(seq_num)+1 As newseq_num FROM winder"
rstUpdRec.Open sqlUpdRec, CurrentProject.Connection,adOpenKeyset,adLockPessi mistic
...
...
...
...

 sqlUpdRec = "SELECT winder_num, machine_num, seq_num FROM winder WHERE seq_num = " & intNewseq_num
rstUpdRec.Open sqlUpdRec, CurrentProject.Connection,adOpenKeyset,adLockPessi mistic

sorry for being such a pain. i've got no other choice and i really have to get this done by this week.:-(




tasha

Tasha on your event properties donot forget the open and close parentheses ():

= Print_OnClick()

To explain to you the code is:
1) We check if there is an existing seq_num already on the form
2) IsNull means empty so we cancel the event
3) IsNull(Machine_num) means there is nothing so we cancel the event as well.
4) If 2 & 3 is false. Means what you are printing has a machine_num and a seq_num.
5) After printing this we need to assign a New_seq_num by calculating
the Max(seq_num) + 1. Then insert it to the winder table.

What I do is ADODB recordset (Active Database Object). It is like a memory function in a calculator. Where you put a calculation in a memory set it aside then retrieve it back.

Let me know if this works.
john

i did put the open and close parentheses ()previously...but it didnt work.

we check existing seq_num already on form...since my print button is in the subform that based on winder table, so i set as:

intseq_num = Screen.winder Subform.seq_num.Value
strMachine_num = Screen.winder Subform.Machine_num.Value
strwinder_num = Screen.winder Subform.winder_num.Value

is that correct?

since my winder table already has records of winders assign to particular machines.(users just have to go to the particular record and print)so how does the seq_num being stored in winder table? i am not getting a clear picture of it...

will it store like this?

winder machine seq_num
w1m1 1 0001
w2m1 1
w3m1 1 0002
w4m1 1
w1m2 2 0003


i suppose it is early morning in US now and you should be sleeping. very sorry to trouble you this time. i really appreciate your help.

tasha

My code was patterned to a form without a subform.
Personally, I have not been using forms with a subform, so my codes
will not be complicated. That is why all my form value is like this --> Screen.ActiveFrom.[Control Name].Value.
What I do is to have all my forms to have only one table source. And just automatically update related tables by code/adodb.
It is easier for me to analyze my table
structure and relationship. Before I use not to do this because
of lack of storage. Now a days most of all computers have huge amount of storage space so I couldn't care less if I have both field names & values in two or more tables.

Since your print button is in the subform that based on winder table, set it up as:

intseq_num = Forms![machine]![winder]!seq_num.Value
strMachine_num = Forms![machine]![winder]!Machine_num.Value
strwinder_num = Forms![machine]![winder]!winder_num.Value

Probably it should work. If not let me sleep on it and discuss it with you tomorrow.

john

right now it is 10:35 p.m. Got to go. Good night.

i did change my code but it still give me the same error. this is the code

Option Compare Database

Public Function Print_OnClick()
On Error GoTo Print_OnClick_Err

    Dim rstUpdRec As New ADODB.Recordset
    Dim sqlUpdRec As String
    Dim intNewseq_num As Integer

    Dim strMachine_num
    Dim strwinder_num

    intseq_num = Forms![machine]![winder]!seq_num.Value
    strMachine_num = Forms![machine]![winder]!machine_num.Value
    strwinder_num = Forms![machine]![winder]!winder_num.Value


    If IsNull(strwinder_num) Then
        DoCmd.CancelEvent
    ElseIf IsNull(strMachine_num) Then
        DoCmd.CancelEvent
    Else

        '-------------------------------------------------'
        ' Create a new seq_num record FROM winder table '
        '-------------------------------------------------'
        sqlUpdRec = "SELECT Max(seq_num)+1 As newseq_num FROM winder"
        rstUpdRec.Open sqlUpdRec, testprojek.Connection, adOpenKeyset, adLockPessimistic

        If rstUpdRec.EOF Then
            rstUpdRec.Close
            Set rstUpdRec = Nothing

        Else
            intNewseq_num = rstUpdRec("newseq_num").Value
            rstUpdRec.Close
            Set rstUpdRec = Nothing
        End If

        '-------------------------------------------------'
        ' Update a new seq_num record FROM winder table '
        '-------------------------------------------------'
        sqlUpdRec = "SELECT winder_num, machine_num, seq_num FROM winder WHERE seq_num = " & intNewseq_num
        rstUpdRec.Open sqlUpdRec, testprojek.Connection, adOpenKeyset, adLockPessimistic

        If rstUpdRec.EOF Then
            rstUpdRec.AddNew
            rstUpdRec("Machine_num").Value = strMachine_num
            rstUpdRec("winder_num").Value = strwinder_num
            rstUpdRec("seq_num").Value = intNewseq_num
            rstUpdRec.Update
            rstUpdRec.Close
            Set rstUpdRec = Nothing

        Else
            rstUpdRec.Close
            Set rstUpdRec = Nothing
        End If

    End If

Print_OnClick_Exit:
    Exit Function

Print_OnClick_Err:
    MsgBox Err.Description
    Resume Print_OnClick_Exit

End Function


testprojek is the name of my database. what shall i put on connection? please check whether i left any changes that i suppose to make. thanks





tasha