Edit a Record from a Table Using a Query to Select the Record

[jeoku]

I have a table called "Submittals" that is placed in a form. I have a List box titled "SubmittalsList" that is based on a query from the table "Submittals" which finds specific records to edit. I want to be able to click on an item in the listbox and be able edit that record in the form, which is also called "Submittals."



My idea was to get some code to pull the primary key from the query and find that specific record and then open that record in the form. I am not good at SQL at all so I could use help creating this code.

[pbaldy]

Have you tried the listbox wizard? I think it will offer the same "Find a record..." option that the combo wizard will offer on a form bound to a table.

[jeoku]

Yes. In order to use that wizard I have to use the bound table, but I want to use the query because I will end up with a lot of records. Does that make sense?

[jeoku]

I think I am close to getting it. Please take a look at this. I don't think my syntax is right though and I don't know what the syntax should look like. Is the command Forms!Submittals!SubmittalList adequate to pull the bound field? Thanks

Private Sub SubmittalList_Click()

Dim SQLStg As String
SQLStg = " SELECT Submittals.ID FROM Submittals "
SQLStg = SQLStg & "WHERE Submittals.ID = " & Forms!Submittals!SubmittalList

End Sub

[rzw0wr]

A sub form may work.
Select the PK you want on the main form from a combo box.
Bind the subform to the main using the PK for the combo box.

Dale

[pbaldy]

Is the command Forms!Submittals!SubmittalList adequate to pull the bound field?

Yes, and now I'll get out of Dale's way.

[rzw0wr]

Paul,
You are not in my way.....

Dale

[jeoku]

I made a form with the query results, which searches by the project title, and then a subform with the table and it is bound to the query results by the project title, which is exactly what I wanted, though I had to change my design a little to get what I wanted. The subform is now shown in the form of a data table. I have a couple of problems:

1. If the query is empty then all of the buttons disapear and there is nothing there! I can't even add a new record
2. How can I delete a record from a data table view in a subform? I think I could modify it similar to what I had before and get it to work, but I still have the other problem for sure.

[rzw0wr]

What buttons? Did I miss something in one of your posts?
Question 1 question:
Does the subform query return anything when the criteria is removed form the query?
How many tables are are in your query?
NOTE: Not all queries are updateable. Some multi-Table queries are not.

I don't usually delete records in a form. This depends on the table(s) in the underlying query of the form.
This can be hard to do if the tables are NOT normalized.

Dale

[jeoku]

There is a lot more to my forms then what I explained because I don't want to bore you with too many details, but basically not every project will have any submittals, meaning sometimes I will need to do a search in this query yielding no results and then be able to add records to the Submittal Table. Only one table is in the query. There are other buttons, reports, tables, and forms as I am actually working on a pretty big project to try to keep track of a lot of different things as well as be able to create a lot of custom reports. Other people will be using this database, though not several at once, meaning I need to make it as fool proof as possible so I am trying to make it so that they can do all their work in forms alone, which makes it more challenging.

[jeoku]

Just about got it. I added the following code to the form to add a new record to the table. The only question I have now is pretty minor: How can I open the form up again so I don't have to click the button to open this form twice? The first time I click it the record is created and the second time it opens.

Private Sub Form_Open(Cancel As Integer)
If Me.RecordsetClone.RecordCount = 0 Then
Cancel = True
Dim dbs As Database
Set dbs = CurrentDb
dbs.Execute "INSERT INTO [Submittals] ([Project Title]) VALUES " & "('" & Forms![Project Screen]![PT] & "');"
dbs.Close
End If

[rzw0wr]

I don't have a clue about what you are talking about jeoku.
Last I knew of you asked 2 questions.

1. If the query is empty then all of the buttons disapear and there is nothing there! I can't even add a new record
2. How can I delete a record from a data table view in a subform? I think I could modify it similar to what I had before and get it to work, but I still have the other problem for sure.

I asked you a question.

Question 1 question:
Does the subform query return anything when the criteria is removed form the query?
How many tables are are in your query?

And you are now asking another question.

I am totally lost.

I am still trying to figure out what it is you want.
Dale

[jeoku]

Sorry. I didn't mean to confuse you. I have a button on a separate form to open up this particular form that I was working on. Based on this code which happens when the particular form is opened,

Private Sub Form_Open(Cancel As Integer)
If Me.RecordsetClone.RecordCount = 0 Then
Cancel = True
Dim dbs As Database
Set dbs = CurrentDb
dbs.Execute "INSERT INTO [Submittals] ([Project Title]) VALUES " & "('" & Forms![Project Screen]![PT] & "');"
dbs.Close
End If

I will add a new record to the table if the query is blank, which will keep me from opening a blank form. The problem is now pretty minor in that if there is no record I have click the button on the other form, which is programmed to open the form I am working on twice. The first time it creates the new record and the second time it will then open the form.

It may be that this is just too confusing to explain without seeing it.

[rzw0wr]

Ok, Let's start again.
You speak of an empty query. Why is the query empty?
Are there no records in the table(s)?

Dale

[jeoku]

I have a table with a list of projects. I have another table with a relationship to this table with a list of submittals. This form opens a query from the list of submittals with a particular project title. Therefore, when I add the first record to the submittal table for a particular project the query will be empty.