transfer value before form closes

[antonyx]

i want to send an id from 'job' to 'job1'.

the job form needs to be closed before the job1 form opens..

i want to do something like this..

job form

Private Sub Form_BeforeUpdate(Cancel As Integer)
DoCmd.RunCommand acCmdSaveRecord
DoCmd.Close
DoCmd.OpenForm "job1"
End Sub

and then on job1 open event use

Me.txtjobref = Format(Forms!job.jobref, "00000000")

the problem is that the job form will be closed so how can i transfer it?

 

[boblarson]

There are two ways that I know of that you can do.

1. Include OpenArgs in the open form code that include the value of jobid and then use code in the load event or open event of the form job1 to assign it (and I think we found out that you might have to set focus on that control before trying to format it from my earlier help).
2. Create a public variable in a standard (not form) module and assign it the value when closing the job form and then read it in when opening job1.

 

[antonyx]

2. Create a public variable in a standard (not form) module and assign it the value when closing the job form and then read it in when opening job1.

ok.. i doubt it is as simple as this.. but i reckon its nearly this simple..

ive saved a module as 'ref' with this code

Option Compare Database

Dim sendref As String

and then..

Private Sub Form_BeforeUpdate(Cancel As Integer)
DoCmd.RunCommand acCmdSaveRecord
Modules!ref.sendref.value = me.txtjobref.value
DoCmd.Close
DoCmd.OpenForm "job1"
End Sub

then on job1

Me.txtjobref.SetFocus
Me.txtjobref = Format(Modules!ref.sendref, "00000000")

if this is completely wrong please be patient with me.. this is the first time i am using individual modules to use in local and global scenarios..

 

[stopher]

I favour Bobs option 2.

As an aside, you can also store data in tables of course. This is particularly useful if you want the data to be available when you've closed/reopened the application, e.g. last record edited

Stopher

 

[boblarson]

In the module you have to declare the variable so that it is available within your scope. So for your purposes, you will need to declare it:

Option Compare Database

Public sendref As String

You have to use the "PUBLIC" keyword instead of DIM as you want to be able to access it outside of your module. If you use DIM, it will be available ONLY to the functions and subs in your module.

 

[antonyx]

ahh ok.. so if i use that..

can i just use 'sendref' anywhere i want in my database.. and it will be that value..


what about setting that value..

Modules!ref.sendref.value = me.txtjobref.value

and reading it..

Me.txtjobref = Format(Modules!ref.sendref, "00000000")

are these correct because i dont think they are>?

 

[boblarson]

No, you don't refer to variables the same way as other objects. They stand alone.

Me.txtjobref = Format(sendref, "00000000")

 

[antonyx]

ok.. it says error 3008

its trying to open the job1 form but the job form is still open..

this is wat i have at the moment..


Form Module

Option Compare Database

Public sendref As String

job form

Option Compare Database

Private Sub Command6_Click()
DoCmd.RunCommand acCmdSaveRecord
Me.txtjobref.SetFocus
Me.txtjobref.Value = Format(sendref, "00000000")
DoCmd.Close
DoCmd.OpenForm "job1"
End Sub

job1 form

Option Compare Database

Private Sub Form_Open(Cancel As Integer)
Me.txtjobref.SetFocus
Me.txtjobref = Format(sendref, "00000000")
End Sub

 

[boblarson]

Try this instead on Job (job1 appears to be correct)

Option Compare Database

Private Sub Command6_Click()
   DoCmd.RunCommand acCmdSaveRecord
       Me.txtjobref.SetFocus
       sendref = Me.txtjobref.Value
       DoCmd.Close
       DoCmd.OpenForm "job1"
End Sub

 

[stopher]

I might be off the mark here but I'd put the close statement in the on activate event of job1. Obviously you have to state the name of the form you are closing:
docmd.close acform, "job"

Stopher

 

[antonyx]

still the same prob.. its here in 2003 and 97 format.. have a look

 

[stopher]

still the same prob.. its here in 2003 and 97 format.. have a look

You have your forms set to lock all records in the table. So you can only open one form or the other. Set the record lock of both forms to "edited record".

Stopher

 

[stopher]

Sorry, one other thing, the following lines are unnecessary imho:
DoCmd.RunCommand acCmdSaveRecord (the record gets saved on close)
Me.txtjobref.SetFocus (for what purpose ?)

Stopher

 

[antonyx]

thank you sir..

 

[antonyx]

one last thing..

in job1.. at the moment it is telling me that i cant create a record because it will create a duplicate value in the primary key..

these two forms populate a single record in a single table..

at the moment im using this..

DoCmd.GoToRecord , , acNewRec

in job form to create a new record.. then in job1 form... the record (and corresponding jobref no) will always exist in the table beforehand..

so on job1 form.. i would need it to realise that it is the record of that jobref.. rather than just setting the jobref to that number..

is there some way i can turn this:

Option Compare Database

Private Sub Form_Open(Cancel As Integer)
Me.txtjobref.SetFocus
Me.txtjobref = Format(sendref, "00000000")
End Sub

into something like this

Option Compare Database

Private Sub Form_Open(Cancel As Integer)
Me.txtjobref.SetFocus
Me.[jobref].DefaultValue = Format(sendref, "00000000")
End Sub

note.. jobref is the field name rather then txtjobref which is the control name for the field

 

[stopher]

I'm not entirely sure what you are asking. I think you want to open job1 at the same record as job (?). You can do this with the openform command:

Private Sub Command6_Click()
Dim stLinkCriteria As String
   DoCmd.RunCommand acCmdSaveRecord
       Me.txtjobref.SetFocus
       sendref = Me.txtjobref.Value
       stLinkCriteria = "[jobref]=" & "'" & Me![jobref] & "'"
       DoCmd.Close
       DoCmd.OpenForm "job1", , , stLinkCriteria
End Sub

See how the stLinkCriteria picks up the jobref from the job form and uses it as a criteria for opening job1.

Stopher

 

[antonyx]

if i do this then would i need to refer to the 'sendref' variable in the separate module?

 

[antonyx]

yes i still can and it works i think

 

[antonyx]

also if i make sure the lock is set to edited record on both forms.. would it be possible to open them both at the same time.. even though they will both be refering to the same record?

 

[antonyx]

sending the id using linkcriteria i dont need it to be saved in the individual module.. it seems to be sending it across ok..

correct me if im wrong