Insert query from filtered results of split form (1 Viewer)

M

moishier

Registered User.
Local time
Yesterday, 21:00
Joined
Apr 17, 2013
Messages
34
Hi,

New with Access forms.

I created a split form with a search box where I can filter the results.

Now I want to create a button that I can insert new records based on the results of the filter (for example, for all those clients in the subform, I want to insert records in a new table of orders saying that they order product A.)

Where do I begin?
 
June7

June7

AWF VIP
Local time
Yesterday, 17:00
Joined
Mar 9, 2014
Messages
5,465
Depends on your data structure. Do you have Orders and OrderDetails tables?
 
M

moishier

Registered User.
Local time
Yesterday, 21:00
Joined
Apr 17, 2013
Messages
34
Just an orders table. I need to batch add all the same records except the customerID.
 
June7

June7

AWF VIP
Local time
Yesterday, 17:00
Joined
Mar 9, 2014
Messages
5,465
Run an INSERT SELECT action that uses the same filter criteria as the form. Something like:

CurrentDb.Execute "INSERT INTO Orders(Product, Customer, OrderDate) SELECT " & Me.tbxProduct & " AS Prod, Customer, Date() As OD FROM Customers WHERE " & Me.Filter

What table is the form bound to? What is the filter criteria?

What do you mean by 'new' table of orders? Why would you want a new table?
 
M

moishier

Registered User.
Local time
Yesterday, 21:00
Joined
Apr 17, 2013
Messages
34
Thanks.

It's a bound form, to a query called "master_list_filtered".

I am filtering the form via an embedded macro, using the "ApplyFilter" condition. (I used this video as a guide: https://www.youtube.com/watch?v=wCPs4aE5I2w)

Sorry, it's not a new table. I am trying to insert new records into an existing table.

What's the best way for me to attach the INSERT statement to the button that creates the records?
 
June7

June7

AWF VIP
Local time
Yesterday, 17:00
Joined
Mar 9, 2014
Messages
5,465
Use button Click event. I don't use macros, only VBA, hence the example given is VBA. Select [Even Procedure] in the event property, click the ellipsis (…) to open VBA editor. Type code.

I don't want to watch YouTube. Why is form bound to filtered query? Post this query SQL statement.
 
M

moishier

Registered User.
Local time
Yesterday, 21:00
Joined
Apr 17, 2013
Messages
34
The query itself is not filtered - it gets filtered by the top part of the form. I want to insert the results of the filter into a table.

My VBA for the event is like this so far, but it does nothing:

Code: 
Private Sub add_complaints_Click()
CurrentDb.Execute "INSERT INTO complaints(unique_account_number, reason_code,missing_from,missing_to,complaint_date) SELECT  master_list_filtered.unique_account_number, 'MC' as reason_code, Date() as missing_from,Date() as missing_to,Date() as complaint_date  FROM master_list_filtered WHERE " & Me.Filter
End Sub

(complaints are the "orders")

What am I doing wrong?
 
June7

June7

AWF VIP
Local time
Yesterday, 17:00
Joined
Mar 9, 2014
Messages
5,465
What is the filter criteria?

If the macro sets the form's Filter property, should be able to grab the criteria string with code. Works for me. But I don't use macros, only VBA.

If you want to provide db for analysis, follow instructions at bottom of my post.
 
M

moishier

Registered User.
Local time
Yesterday, 21:00
Joined
Apr 17, 2013
Messages
34
Okay, I'm making lots of progress. I have the filter working in vb, and the insert statement working.

I am having some trouble with 2 things:

1. Getting an integer sum of records returned by the filter and then using it in the msgbox (in place of the X)
2. Adding a validation rule that you can't press the command button unless a filter is first applied.

Here is the vb code:

Code: 
Private Sub Command51_Click()

Me.Filter = "([Address_1] like '*" & [Forms]![bulk_add_complaints2]![search_street] & "*') and ([city] like '*" & [Forms]![bulk_add_complaints2]![search_city] & "*')"
Me.FilterOn = True
Me.Requery

End Sub

Private Sub add_complaints_Click()

Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "Do you want to bulk-add complaints to X accounts?"
Style = vbYesNo + vbDefaultButton2
Title = "Bulk-add complaints"
Ctxt = 1000

Response = MsgBox(Msg, Style, Title, Help, Ctxt)
If Response = vbYes Then
CurrentDb.Execute "INSERT INTO complaints(unique_account_number, reason_code,missing_from,missing_to,complaint_date,complaint_text) SELECT  master_list_filtered.unique_account_number, '" & [Forms]![bulk_add_complaints2]![apply_reason] & "','" & [Forms]![bulk_add_complaints2]![apply_from] & "','" & [Forms]![bulk_add_complaints2]![apply_to] & "','" & [Forms]![bulk_add_complaints2]![apply_date] & "','" & [Forms]![bulk_add_complaints2]![apply_text] & "'  FROM master_list_filtered WHERE " & Me.Filter
MsgBox "X Complaints Saved", vbInformation, "OK"
Else
Exit Sub
MyString = "No"
End If

End Sub


Thanks for your help!!
 
June7

June7

AWF VIP
Local time
Yesterday, 17:00
Joined
Mar 9, 2014
Messages
5,465
MsgBox Me.RecordsetClone.RecordCount & " Complaints Saved", vbInformation, "OK"


Set add_complaints Enabled property as No in design then in Command51_Click code:

Me.add_complaints.Enabled = Me.Filter <> ""
 
Last edited:
M

moishier

Registered User.
Local time
Yesterday, 21:00
Joined
Apr 17, 2013
Messages
34
So far, so good!

Thanks very much for you help!
 
You must log in or register to reply here.

Similar threads

S
hide a record while it's being edited in a form
2 3
Replies
50
Views
2,026
ebs17
E
B
Solved Filtering Subform based off carried over data
Replies
5
Views
327
Pat Hartman
P
mike60smart
Insert
Replies
3
Views
316
Pat Hartman
P
J
Solved Focus on continuous form
2
Replies
25
Views
1,100
Pat Hartman
P
D
Automatic form with calculations based on two tables
Replies
15
Views
235
arnelgp
arnelgp

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom