Query / Form Question

[LATECHmatt05]

Hello All,

I have created a form to query a table with this forum's help I might add thanks. Well now my question is once I get the records I need from the query I want to use another form to modify a few of the fields in the record and then save the modified record as a new record. I am not sure if I can do this. Just to let y'all know the records do have an autonumber field, but it is not the primary key. My question is how do I get the records my query form retrieves to open in form view and how do I code an action button to save the modified record as a new record in the table I just queried. Thanks very much in advance.

 

[ekwstats]

You could create a copy record button and then modify the copied record.
Private Sub cmdCopy_Click()

DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdPasteAppend
MainRequery

End Sub

Function MainRequery()
Forms![frmHome]![HomeSubForm].Form![MainSubform].Form.Requery
End Function

If the fields that you want to carry over to each new record are always the same you could make sure those values are added for each new record (will explain if this is what you want) and then just change the fields that are not static.

 

[LATECHmatt05]

I do have some field that will be static and some that will not be actually it is about half and half. Now the other thing I need to know is after my code how do I open the records query in form view? Here is the code I have for my query form; it works but I doesn't open the record in form view.
Private Sub cmdOK_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Set db = CurrentDb
Set qdf = db.QueryDefs("masterq")
strSQL = "SELECT Begin.* " & _
"FROM Begin " & _
"WHERE Begin.[date] BETWEEN #" & DateA & "# AND #" & DateB & _
"# AND (Begin.UnitName='" & Me.UnitA.Value & _
"' OR Begin.UnitName='" & Me.UnitB.Value & _
"' OR Begin.UnitName='" & Me.UnitC.Value & "');"
qdf.sql = strSQL
DoCmd.OpenQuery "masterq"
DoCmd.Close acForm, Me.Name
Set qdf = Nothing
Set db = Nothing


Thanky you for your very insightful help I am trying to get up to speed on all this as it is my summer internship assignment.

 

[ekwstats]

I might be missing something, but a form is just a way to retrieve into whether it be via a query or straight from a table. I don't really understand what you mean when you say a query form other than it happens to use a query to retrieve it's information.

You can open a form in several different views....datasheet view, form view, pivot table, etc.

Single Form -(Default) Displays one record at a time.
Continuous Forms -Displays multiple records (as many as will fit in the current window), each in its own copy of the form's detail section.
Datasheet -Displays the form fields arranged in rows and columns like a spreadsheet.
PivotTable -Displays the form as a PivotTable.
PivotChart -Displays the form as a PivotChart.

If you want to use the single form view you can put a button on the bottom of the form that copies the previous record and then make appropriate changes (code I posted above). Or you could use the datasheet view and just copy and paste the record you want to replicate.

I also have a form that opens in data sheet view. I have it setup so that when I double click the Name field of any record it copies that record. It's a bit easier than copy paste.

Private Sub cmbNameID_DblClick(Cancel As Integer)
If Me.cmbName <> "" Then
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdPasteAppend
MainRequery
Else
MsgBox "The Name can not be blank."
End If
End Sub

 

[TheAceMan1]

How are ya LATECHmatt05 . . . . .

Backing up [blue]ekwstats[/blue], your not understanding some [blue]Terminology here[/blue], and its misleading you.

[blue]I have created a form to query a table[/blue]

What you've done, [purple]is create a form to view a table[/purple], using a query as the [blue]RecordSource[/blue]. The [blue]RecordSource[/blue] can also be a [blue]Table[/blue] or [blue]SQL[/blue]

[blue]how do I open the records query in form view?[/blue]

There's no such thing as a [purple]records query![/purple]. If you understood my answer to the previous quote, the answer should be clear . . . . . . Set the [blue]RecordSource[/blue] to the query or SQL . . . . .

See Ya! . . . . . .