Problem with Form Update

[utzja1]

I've hit an unexpected snag in producing a report. I have a table with 15 structures (records) that have a number of metrics (fields, e.g., location, condition, size, etc) associated with them. I created a form that I can use to update an individual record; the form is populated with a combo-box that uses the structure number as the lookup value. After using the form, I can verify in the table that the appropriate updates are reflected in the new data.

The data source for my report is a parameter-based Select Query (parameter is the structure number). The problem I have is that when I use my form to update individual records, my query will no longer locate these records and just returns a blank datasheet. I suspect that when I update the data using the form, some property is being altered causing the query to pass it by. When I run my report on a record that I entered directly in the table, it works perfectly.

Has anyone encountered this before? I'm relatively new to Access and appreciate any and all feedback.

 

[utzja1]

Thanks for the reply. I double-checked my form and my combo-box was unbound. I edited a record using the form, then closed the form, verified the changes were made to the right record in the table, then opened the form back up to edit the same record. All the data came back when I did this, so I know that the first form is not having problems finding the data.

I queried the database for each individual record (parameter SELECT query, using the structure number), and half of the queries returned no results. I took one of the records that did return data and used my form to edit several fields in that record. I then re-queried it, and it returned no data. Attached is a PDF that may shed a little light on what I'm doing. It seems like when I save and exit the EditBridgeRecord form, something is changed that does not permit the query from finding the record.

Your comments are much appreciated.

 

[boblarson]

Many times people forget that if they are working on a record and then open a form or report it won't have the data just worked on because they didn't save the record first (either by moving to a new record or issuing a save command).

I prefer to issue a save command like this:

If Me.Dirty Then Me.Dirty = False

simply because it will only try to save if something is there to save (either a changed field or new record) where using something like

DoCmd.RunCommand acCmdSaveRecord

will always attempt a save even if there isn't something to save.

 

[utzja1]

Thanks for the feedback, Bob, and I may have to steal that technique for future assignments. In this instance, though, the form is only used to edit existing records. Even if the user doesn't change anything, there will be something to save.

Somewhere between saving the edited record and querying the updated table, there is a breakdown and I am giving myself a case of flat-head syndrome trying to find it. I can query for one record and it shows up perfectly in the report, edit the same record with this form, and then as far as the Query is concerned, it drops off the face of the earth. But the data is still in the table, and other forms can retrieve it. I'm puzzled.

Thanks for your help, though. I'll keep digging.

 

[boblarson]

 

[utzja1]

Oh, boy, that would not have occurred to me. Thanks for passing along that nugget! Could that possibly explain why the query is not finding these records?

Then to save the updated record, does the user just need to exit the form (assuming Access will save it automatically on close)?

Thanks much.

 

[boblarson]

Oh, boy, that would not have occurred to me. Thanks for passing along that nugget! Could that possibly explain why the query is not finding these records?

Then to save the updated record, does the user just need to exit the form (assuming Access will save it automatically on close)?

Thanks much.

Yeah, it should be saving on closing. The query should be running AFTER the close of the form, which I'm guessing you already have that. So, if that is true then not sure why it wouldn't reflect changes.

 

[utzja1]

I seemed to have tracked the problem to the Join operation in the query (it took a fair amount of effort to be able to write that sentence). I trimmed the Query down to search just one table and my data showed up. I then edited the data again with what I thought was my problem form, and it pulled the updated data into the report correctly. So...

I need to pull data from three tables to generate the report I want for my client, but I've narrowed the playing field down considerably. Thanks for your help.