VDA entering data

[2ippy]

In a form, i have got a button to create new item. It VBA's to a new form and new record.

How do i get it to auto insert the customer ID, as it'll be showing the current customer on the 1st form.

 

[jwhite]

In otherwords, I assume you mean you have an Add button on form1 -- when pressed, it opens Form2 to add a new record... Sounds also like you are adding purchases or something-like....

Look in to the "stLinkCriteria" parameter of the DoCmd.OpenForm syntax - here is where you can pass the CustomerID value that is selected on Form1. And in Form2's RecordSource, you will refer to the proper table which also has CustomerID in the fieldlist.

If new "items" are added in Form2, and Form1 has data of these items listed, and new records are added, then you will want to "Forms!Form1.Requery" OnClose (Event) of Form2.

 

[2ippy]

This is the code i used, but I think it doesn't like the setlink criteria.

Private Sub CreateNewItem_Click()
    Dim stDocName As String
        Dim stLinkCriteria As String
        stDocName = "Customers Create Item"

        If Me.CustomerID <> "" Then
            stLinkCriteria = "[CustomerID]=" & Me![CustomerID]
            DoCmd.OpenForm stDocName, , , stLinkCriteria ', acReadOnly
            DoCmd.RunCommand acCmdRecordsGoToNew
      Else
          MsgBox ("You must select a Customer to create a new item")
      End If
End Sub

any surgestions?

 

[gemma-the-husky]

is customerid text or a number?

 

[2ippy]

in customers table it's a auto number and key. in Items table, it is just a number from customers table.

 

[2ippy]

anyone got any ideas? I'm still stuck on this.

 

[gemma-the-husky]

this wont work

stLinkCriteria = "[CustomerID]=" & Me![CustomerID]
DoCmd.OpenForm stDocName, , , stLinkCriteria ', acReadOnly

;lines 1 and 2 are ok, syntactically (is the last bit really commented out?)

DoCmd.RunCommand acCmdRecordsGoToNew
line 3 will operate on your CURRENT FORM, not on the form you just opened.

if you want THAT form to go to a new record, you need something in the open event of THAT form.

 

[Bat17]

StrCriteria is a Where clause, it will only filter to certain data it wont add dta to a new record.
you need to either open the form using openArgs or open it using the acFormAdd data mode and poking the data directly in the field you want it.

Peter

 

[2ippy]

I know what you saying, but putting it down in VBA is another ting to me

 

[2ippy]

Private Sub CreateNewItem_Click()
    Dim stDocName As String
    Dim stLinkCriteria As String
    Dim SupID

    If Me.CustomerID <> "" Then
        SupID = Me![CustomerID]
        stDocName = "Customers Create Item"
        DoCmd.OpenForm stDocName, , , , acFormAdd
        Forms!Customers Create Item!CustomerID = SupID"
    Else
        MsgBox ("You must select a Customer to create a new item")
    End If
End Sub

Found some code fixing in other post. But because the name of the form has spaces the line

Forms!Customers Create Item!CustomerID = SupID"

doesn't work.

any surgestions?

 

[Bat17]

Square brackets are your friend here

Forms![Customers Create Item]!CustomerID = SupID

 

[2ippy]

tyvm, it's working. Just needs some tweaking.

what is the code for on focus to make a form update after you know tables data has been changed.

Also how do you make a form only save the data into a table if all the fields are enters or a button is pressed?