sending a variable to a form

cjamps

Registered User.
Local time
Today, 08:03
Joined
Feb 2, 2009
Messages
29
I have a command button on a form to add a task
Private Sub AddTaskBttn_Click()
Dim Result As String
Dim Sn As String
Sn = Me.cmbDSerialNumber.Column(0)
DoCmd.SetWarnings False
DoCmd.RunSQL "Insert Into Tasklist( TSerialNumber ) Select '" & Sn & "' As Expr;"
Result = DLookup("[TSerialNumber]", "TaskList", "[TSerialNumber]='" & Sn & "' ")
DoCmd.OpenForm "Task", acNormal, , "TaskList.TSerialNumber" = Sn, acFormAdd, acDialog, Sn

DoCmd.SetWarnings True
End Sub
On the task form I have the following procedure
Private Sub Form_Load()
If Not IsNull(Me.OpenArgs) Then
' If form's OpenArgs property has a value, assign the contents
' of OpenArgs to the CompanyName field. OpenArgs will contain
' a company name if this form is opened using the OpenForm
' method with an OpenArgs argument, as done in the Orders
' form's CustomerID_NotInList event procedure.
Sn = Me.OpenArgs
Sn = Me![TSerialNumber]
Me![TSerialNumber] = Me.OpenArgs
Me.Requery
End If
End Sub
A new record is being inserted with the proper Tserialnumber but the rest of the fields are blank. These fields are on the task form. However when
the record on the task form is saved the Tserialnumber is blank. In the immediate window it seems that sn still holds a value when the form is opened.
Any help would greatly be appreciated.
 
How about telling us in words what you believe the following code is doing:
Sn = Me.OpenArgs
Sn = Me![TSerialNumber]
Me![TSerialNumber] = Me.OpenArgs
Me.Requery
 
Thank you for responding. I am trying to pass a variable to a form. It seems to me that when I open the form only the sn has the right value. The openargs is null as well as the TSerialNumber. However the previous record does contain the TSerialNumber. What I am trying to accomplish is that sn will pass on the value to TSerialNumber. I assumed the dlookup would get me to the right record.
 
Thank you for responding.

Since sn seems to be the only variable having the right value I was hoping to pass it on to the Task form. But openargs and tserialnumber both don't have values. The serial number is saved in the previous record through the insert command. So I thought the dlookup would position it to the proper record since result has the proper value as well.
 
I'm struggling to see why you want to do all this.

Perhaps you could explain what you are wanting to do at table/record level. What tables are you dealing with and what are the relevant fields in each table? What are the primary keys?

I can see you have a table called Tasklist which has a field call TSerialNumber but that's about it.

Chris
 
I appreciate your consideration and your patience. I am trying to teach myself ms access with as little means as possible. I find the best way is to do this hands on.

I have 2 databases. One is dockets and the other is tasks. Each docket could have many tasks. The primary key in dockets is DSerialNumber. The relationship is to TSerialNumber. I have a form with 2 cascading combo boxes. However if the user wants to add a task for a certain docket he chooses the correct serial number from the first combo. Then it is supposed to add a record to the task (child) database. It does so with the insert. But I want to open a form to fill out the rest of the fields and I am having a problem passing the variable to the form.
 
I appreciate your consideration and your patience. I am trying to teach myself ms access with as little means as possible. I find the best way is to do this hands on.

I have 2 databases. One is dockets and the other is tasks. Each docket could have many tasks. The primary key in dockets is DSerialNumber. The relationship is to TSerialNumber. I have a form with 2 cascading combo boxes. However if the user wants to add a task for a certain docket he chooses the correct serial number from the first combo. Then it is supposed to add a record to the task (child) database. It does so with the insert. But I want to open a form to fill out the rest of the fields and I am having a problem passing the variable to the form.
Do you really have 2 databases or do you have 2 tables in 1 database?
 
I appreciate your consideration and your patience. I am trying to teach myself ms access with as little means as possible. I find the best way is to do this hands on.

I have 2 databases. One is dockets and the other is tasks. Each docket could have many tasks. The primary key in dockets is DSerialNumber. The relationship is to TSerialNumber. I have a form with 2 cascading combo boxes. However if the user wants to add a task for a certain docket he chooses the correct serial number from the first combo. Then it is supposed to add a record to the task (child) database. It does so with the insert. But I want to open a form to fill out the rest of the fields and I am having a problem passing the variable to the form.
Assumng you actually mean tables and not databases then use a form/subform to add the tasks to the selected docket.

What are the combo boxes used for. I assume 1 for the docket but the other?
 
Sorry. I have 2 tables in a database. The Dockets table has the serialnumber, Field is Dserialnumber which is the primary key. TSerialnumber
is in the Task Table and is related to DSerialNumber. The Dockets table has the fields regarding the Inventor and Examiner and Attorney
information. The Task table contains the fields regarding the mailing date, duedate and billing information for each task. This is the information
that I wanted to be saved in the form.
 
One combo box is used for the dockets. The other is used for the tasks. If the user chooses the correct serial number then there are a list of
tasks. The user could choose to add a task by pressing the correct command button. Is there a way I can upload or email the program so someone
could tell me if my approach is right?
 
SOLVED!!

A reply from a different forum

So you're basically trying to (a) add a new record to the TaskList table, then open your "Task" form to further edit that record? If that's the case, then comment out these two lines;

'DoCmd.RunSQL "Insert Into Tasklist( TSerialNumber ) Select '" & Sn & "' As Expr;"
'Result = DLookup("[TSerialNumber]", "TaskList", "[TSerialNumber]='" & Sn & "' ")

And alter your OPenForm:

DoCmd.OpenForm "Task", acNormal, , , acFormAdd, acDialog, Sn

Combined with the code you have in the Task form's Load event (you haven't declared the "Sn" variable in that procedure, by the way, but it may be declare elsewhere), this should add a new record to tTasks, and your user can then fill out further data.

The acFormAdd argument tells Access to open the form in DAtaEntry mode ... this is likely the reason why you are getting duplicates in your tables - the code on the Dockets form has added a record, and then your OpenForm call (with the acFormAdd argument) adds another record.
 

Users who are viewing this thread

Back
Top Bottom