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

[rzw0wr]

jeoku, Please answer, Are the tables in your query empty of data?



Dale

[jeoku]

The table has data. The query may or may not be empty based on what records I need to add/edit.

[rzw0wr]

Thank you.
What criteria do you have for the query?


Dale

[jeoku]

Criteria: [Forms]![Project Screen]![PT]

Basically finds a specified project. There is a previous form where you search for a specific project.

[rzw0wr]

Thank you.
Now what is the problem and what would you like to do?

Dale

[jeoku]

Pretty minor now, I have some SQL code so that when I open the form it will check to see if there are any records. If there is not it will add a new record with the Project Title and everything else blank. The problem is that the first time I click the button to open the form the record is created, but the form is not opened. The next time I press the button it is opened as normally. I would like to edit it so that I do not have to click the button twice.

[rzw0wr]

If we are talking about the same query. It will always open empty.
If the query we have been discussing is the underlying query for the form your are trying to open there can be no data in it because of the filter criteria you have in the query that refers the the form you are trying to open.

Have I missed something? are we still talking about the same query and form?

Dale

[jeoku]

Correct. This code checks if there are no new records and then creates a new record if there is none. As I said though I have to open up the form twice. The first time the record will be created and the second time the form will actually open. I am hoping for a way to do this all in one step, but if there isn't a forseen easy way I could live with that.

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]

If Me.RecordsetClone.RecordCount = 0

This will always return a false, 0 null, empty.
You are asking for a clone of the dataset of your form. The form is empty. Therefore the recordset is empty. and always will be with the criteria you have in your query.

IF you are getting the data for your form from this query, and the query is empty, where will you get the PT data from?

Dale

[jeoku]

Forms![Project Screen]![PT]

This data is being pulled from the previous form. The Form "Project Screen" opens up the form "Submittals" which is the one in question.

[rzw0wr]

OK, I think I am getting a clearer picture. My mistake.
You have form Project Screen opened. You selected PT from this form.

Can you upload your data base.

I am not sure I can keep this all straight.

Dale

[jeoku]

This might make it even more confusing, but I attached it lol. The forms in question is "Project Screen" and "Submittals." There are so many dang reports, tables, queries, and forms in this database that you might get lost quickly.

[rzw0wr]

Likely looks like mine. Not a problem.

No body's database looks good when in development.

Give me a little while. May be tomorrow.

Dale

[rzw0wr]

Looked your database over.
It has a few problems that are beyond my abilities to repair.

Possibly one of the more experienced members can take a look.


Dale

[jeoku]

Agreed that it has some problems. Thanks for looking it over at least. It is going to be a work in progress to find issues and fix them. I have never really worked with access before this point so I am applying new tricks to my database as I learn them.