Add Records to Query from a FORM

[sud2017]

I have created a query from multiple tables. I want to add records to the fields of this query from the textboxes in a form. I have added the fields in the FORM from the query fields and created a command button to add new record. when I click the command button, it says 'cant goto a specified record'. Code on the Click event of the command button looks alright. Query is updatable as I can add records manually into the query.

Code in the command button:



Private Sub Command33_Click()
DoCmd.GoToRecord , , acNewRec
Me!QuoteNo.SetFocus
End Sub

Forms allow addition property is set to yes.

I'm doing a trial now so please ignore the command button name.

Wondering if anything else is required to add records to the query. Thank you very much for any possible help.

[June7]

Convention for data entry is 1 table for 1 form. Why do you have form bound to a multi-table query? Lookup tables can be included in form RecordSource but only to display related data, not to enter/edit data to the lookup tables.

If you set QuoteNo textbox TabOrder property to 0, should not need the SetFocus code.

[John_G]

when I click the command button, it says 'cant goto a specified record'

Is the "Allow Additions" property of your form set to "Yes"? It has to be if you want to be able to add new records.

[sud2017]

Thank you for your help. I got rid of query and used 1 table in subform. I am able to add records from the main form to the subform now. one thing which I still need help for is how to FILTER records of subform based on the combobox selection in my main form. combobox has PKand subform has corresponding FK for field called OppID. Anything with properties or codes to include? thank you for any possible help.

Convention for data entry is 1 table for 1 form. Why do you have form bound to a multi-table query? Lookup tables can be included in form RecordSource but only to display related data, not to enter/edit data to the lookup tables.

If you set QuoteNo textbox TabOrder property to 0, should not need the SetFocus code.

[sud2017]

further to my last comment, I have below code in afterupdate event of my combobox in main form, which is giving a 'runtime error 3071 - the expression is typed incorrectly or is to complex to be evaluated.....'

[June7]

What do you mean by 'able to add records from the main form to the subform'?

Why do you need to filter subform? Set the subform container Master/Child Links properties and the subform will only show records related to main form record. Is the main form BOUND?

You did not post code.

[sud2017]

sorry the code:

Private Sub Combo31_AfterUpdate()
Forms!testquoteform3!Query1subform3.Form.Filter = "[OppID_FK] = '" & Me.Combo31 & "'"
Forms!testquoteform3!Query1subform3.Form.FilterOn = True

End Sub

how to check if main form is bound? The fields on main form are added from a table.

[June7]

So the main form has a RecordSource and one of the fields is the PK?

Apply filter to main form and the subform will synchronize if you set the Master/Child properties.

The combobox must be UNBOUND.

[sud2017]

Mainform has a RecordSource which is say table 1. but the combobox from where I am trying to filter the subform has a RecordSource from another table (say table 2). The select values from Combobox are stored in table 1. From the same combobox I was trying to filter the subform records.

However, I am fine with adding an UNBOUND combobox to filter records of subform, but because the values of combobox are not from table 1, the link master fields doesn't show the combobox fields because its from table 2. is there a way to achieve it?

[June7]

I am confused.

Combobox has RowSource, not RecordSource.

A control used to enter filter criteria must be UNBOUND otherwise you change the record.

Most common use for a form and subform is to relate 'parent' and 'child' data. As example, main form bound to Orders table and subform bound to OrderDetails table.

What is the relationship of your main and subform data sources?

[sud2017]

Thanks for your help June7. I have to fix a few things in my db to have a proper master/child relationships between form/subform. I got the way forward though. Thanks again.