Hi Veli
1.. Set the Button you are using to run your macro on form 1 to run [Event
Procedure]
'Open the [Event Procedure] then Copy and paste this code to the event
procedure.
'######################################
'Designed by James Mc Diarmid for Velli Izzet
' Set Parameters for this procedure
Dim Mydb As Database 'Required Database
Dim Rs1 As Recordset 'First Recordset
Dim Rs2 As Recordset 'Second Recordset
Dim InvID1 as long 'Used to get Invoice number
from Form2
Dim ItemID1 As Long 'Used to get Item Identifier from Form1
Dim ItemPrice1 As Currency, SQL 'Used to get Item Price from Form1
Set Mydb = DBEngine.Workspaces(0).Databases(0)
InvID1 = [Forms]![Form2]![InvID] 'Get Invoice Number from Form2, Modify
[Form2] if necessary, should
be the name of
form2 in your database. Modify [InvID], Should be the
name of the
field on form2 that shows the invoice number.
ItemID1 = 0 'Ensure that in event of
error ItemID will have a numeric value
ItemPrice1 = 0 'Ensure that in event of
error ItemPrice will have a numeric value
If Not IsNull([Forms]![form1]![ItemID]) Then ItemID1 =
[Forms]![form1]![ItemID]
If Not IsNull([Forms]![form1]![ItemPrice]) Then ItemPrice1 =
[Forms]![form1]![ItemPrice]
'Check that Values have been passed through
If ItemID1 = 0 or ItemPrice1 = 0 then
Msgbox "No Item has been selected or the Item Price is 0"
Exit sub
End if
'This statement will check for duplicate items on Inv Item List.
SQL = "Select InvID, ItemID from tblInvItems WHERE ItemID = " & ItemID1 & "
AND InvID = " & InvID1
Set Rs1 = Mydb.OpenRecordset("tblInvItems")
Set Rs2 = Mydb.OpenRecordset(SQL)
If Not IsNull([Forms]![form1]![ItemID]) Then
If Rs2.BOF Or Rs2.EOF Then
Rs1.AddNew
With RS1
!InvID = InvID1
!ItemID = ItemID1
!ItemPrice = ItemPrice1
End With
Rs1.Update
Else
MsgBox "This Item Already Exists on this invoice Adjust Quantities
if Required"
End If
End If
Forms![Form2]![Form2Subform].Form.Requery
'End Code
'#############################################
Veli,
You will need to modify the various parameters to coincide with the names of
tables, forms etc in your database.
I hope this helps you out.
Regards
James Mc Diarmid
--
James Mc Diarmid
Veli Izzet said:
Thanks,
The procedure is as you have understood.
I am not good at VB, so I created a command button macro using setvalue to
transfer the ID and the sales price from form1 to the subform of form2.
I cannot however requery form2 (and the subform) with the same macro, i.e.
I cannot requery form2 from form1.
So I put a requery command button on form2 and after input manually
requery the form. This is absurd, and stupid and there must be a better
way- this is what I am seeking.
Regards,
Hi Velli
Firstly apologies if this does not work correctly - it is my first reply
to any newsgroup
To confirm understanding of your process:
1. Open Form2 with form2.subform.
2. Open form1 to select items to be inserted into form2.subform.
3. Select item/s from form1 (Being a sales invoice there could be
many items selected).
4. After Selection return to form2.subform - edit items listed to
create invoice details.
If this is correct then the following should hold true for you.
Modify macro button on form 1 to run [Event Procedure]
create parametres to accept relevant information from form2 eg: DocID or
whatever unique identifier you are using to identify your invoices.
these parametres should be the same as the child master relationship
between form2 and form2.subform
create an addnew query to write this data as well as the item identifier
into the underlying table of form2.subform.
requery form2.subform
all data should be there and ready for invoicing.
James Mc Diarmid
Hi all,
I want to pass data from one form to the subform of another form. For
this I use a command button with a macro that uses the "SetValue" and
transfers the data of TakiID from frmForm1 using
Forms!frmForm2!subfrmForm2Detail!TakiID.
The data gets transferred to where I want but I have to insert the new
record manually, and I cannot insert a new record after the update.
Form2 is the sales form, Form2Detail is the sales detail form and form1
is a form where I look and choose the goods.
Thanks for any help,
Veli Izzet
