Form not updated after adding a record

[ZikO]

Hi,

In one form, I have to click on button that opens the other form. Each form is bound to its table and those two tables are linked by Primary [RaDAR_Id] and Foreign [radar_id] fields. I would like the code to:
* open the other form
* move to the first record that has [radar_id] = [RaDAR_Is] if exists
If there is no any record in the other form that contains [radar_id] = [RaDAR_Id] then I want to create such record. So I am using the code (below). The problem is that the other form is not being updated. Please find the attached figure to see what I get. I don't know what i am doing wrong here. Thanks for any clues on that.

Private Sub cmd_edit_usage_Click()
'    DoCmd.OpenForm "frm_edit_usage"
    Dim lngRadarId As Long
    Dim patientId As Long
    Dim rsUsage As DAO.Recordset
    Dim rsUsageWrite As DAO.Recordset
    
    lngRadarId = [Forms]![frm_main]![RaDAR_Id]
    DoCmd.OpenForm "frm_edit_usage"
    Set rsUsage = [Forms]![frm_edit_usage].RecordsetClone
    rsUsage.FindFirst ("[radar_id]=" & CStr(lngRadarId))
    If rsUsage.EOF Then
        With [Forms]![frm_edit_usage]
            Set rsUsageWrite = .Recordset
            rsUsageWrite.AddNew
            rsUsageWrite![RaDAR_Id] = lngRadarId
            rsUsageWrite.Update
            rsUsageWrite.Bookmark = rsUsageWrite.LastModified
            rsUsageWrite.Close
            Set rsUsageWrite = Nothing
        End With
    Else
        [Forms]![frm_edit_usage].Bookmark = rsUsage.Bookmark
    End If
    
    Set rsUsage = Nothing
End Sub

The link to the screenshot

 

[JHB]

What is the recordsource for the form "frm_edit_usage"? Show the field names.
What is the controlsource for the different controls on the form.

 

[ZikO]

Hi,
Thanks for a quick answer. I am not sure if I understood the question. I'll try to answer the best I can.

What is the recordsource for the form "frm_edit_usage"? Show the field names.

Well. The Recordsource is taken from [frm_edit_usage]--the RecordsetClone. I have just realised the Recordsource for this form is QUERY:

SELECT tbl_usage.radar_id, tbl_usage.usage_type_id, tbl_usage.person, tbl_usage.usage_date
FROM
(
   tbl_patients INNER JOIN tbl_RaDAR
   ON tbl_patients.patient_id = tbl_RaDAR.patient_id
) INNER JOIN tbl_usage
ON tbl_RaDAR.RaDAR_Id = tbl_usage.radar_id;

What is the controlsource for the different controls on the form.

The top combo box's Row source is a QUERY:

SELECT DISTINCT tbl_patients.study_id, tbl_RaDAR.RaDAR_Id
FROM tbl_patients INNER JOIN tbl_RaDAR
ON tbl_patients.patient_id = tbl_RaDAR.patient_id
ORDER BY tbl_patients.study_id;

The Row source for the combo box in second row [usage_type_id] is a QUERY:

SELECT tbl_usage_types.usage_type_id, tbl_usage_types.usage_type
FROM tbl_usage_types
ORDER BY tbl_usage_types.usage_type;

 

[JHB]

To find the control source, open the form in design view, mark the control, open the property sheet, choose the data tab, then the control source is the first property shown and the names should be the same as from the select statement in the query.
Ex. The control source for the Radar Id must be "radar_id".
I've attached a picture from a control I've on a form.
Else post your database with some sample data, (zip it).

 

[RainLover]

The first screenshot shows that you have two Controls that have incorrect Control Sources. Go to design and fix it. You should not see #Name. You should see some data if it exists.

Your Code opens a different form. Then you go off and do various things.

I would open the other Form and then close the sub.

When the new form opens I would create Record Sets etc A lot easier.

You last Post also has a Pic. You use ID as a control source name. (A Field Name) Please give this and all other objects proper names. It would be hard to think of a worse name than ID. If you used this convention you would end up with a Database that could easily have 100 Fields called ID. This would be very confusing.

 

[ZikO]

To find the control source, open the form in design view, mark the control, open the property sheet, choose the data tab, then the control source is the first property shown and the names should be the same as from the select statement in the query.
Ex. The control source for the Radar Id must be "radar_id".
I've attached a picture from a control I've on a form.
Else post your database with some sample data, (zip it).

Thanks JHB.
I find all fields correctly assign to edit boxes. The names are the same as the fields. When I open this form via ACCESS everything looks all right. Records are read correctly from the [tbl_usage]. The problem is when the code adds a new record and tries to find a bookmark of the new record.

The form in Design View

EDIT:
I have attached the link to the database. Thanks for taking a look on it. I really appreciate it ! The database is in ACCESS 2010.
The link to the database

 

[RainLover]

Ziko

I feel that you are making things more difficult than what needs to be.

I can't download your Database as I don't have 2010. It would be good if you converted to 2003.

If you are using bound form you really don't need all that code. Have you thought of making that second form a sub form of the first.

If you can't convert to 2003 then could you supply a pic of your relationships.

 

[ZikO]

Ziko

I feel that you are making things more difficult than what needs to be.

I can't download your Database as I don't have 2010. It would be good if you converted to 2003.

If you are using bound form you really don't need all that code. Have you thought of making that second form a sub form of the first.

If you can't convert to 2003 then could you supply a pic of your relationships.

Hi RainLover,
I did not try to make a subform. Perhaps it is better. I would like to know one thing. If I make the other form a subform, how can I filter out records so that only those are visible whose [radar_id] = [RaDAR_Id] etc.

This is the link to the database converted to version 2003

 

[JHB]

...
I have attached the link to the database. Thanks for taking a look on it. I really appreciate it ! The database is in ACCESS 2010.

I've looked at your database and got it to run. It is not the way I would do it, but you know your business best. The database is attached.
Some shorts notes for clarifying:
Don't convert a number to a string, if the table field type is a number.

rsUsage.FindFirst ("[radar_id]=" & CStr(lngRadarId))

Correct is:

rsUsage.FindFirst ("[radar_id]=" & lngRadarId)

And if the table field type was text, then you have to surround the number by single quarto (') like below.

rsUsage.FindFirst ("[radar_id]=[COLOR=Red][B]'[/B][/COLOR]" & CStr(lngRadarId) & "[B][COLOR=Red]'[/COLOR][/B]")

The correct statement in connection with FindFirst is NoMatch and not EOF!

    If rsUsage.NoMatch Then

You can see the rest I change in the code.

 

[ZikO]

Hi JHB,
I am really glad you made it work. Many thanks for fixing this. Just the last question, do you think the code did not work because of the issues you have just pointed out or was there something else?

Cheers!

EDIT: I feel like I need to buy and read a good textbook. There are many issues and traps I cannot learn from "try and error". Would you suggest something good? Having said that, I understand there is no the ultimate book that can give me everything and I am not looking for it but I'd like to have similar points to yours. Thanks.

 

[JHB]

Hi JHB,
I am really glad you made it work. Many thanks for fixing this. Just the last question, do you think the code did not work because of the issues you have just pointed out or was there something else?

One issues was the NoMatch where you have set it to EOF.
Another two were these, where the Requery wasn't in your code:

            .Form.Requery
            .Bookmark = rsUsageWrite.LastModified

And then you have set the Set rsUsageWrite = .Recordset it should be the Set rsUsageWrite = .RecordsetClone.
About the books - I can't give any advice, the most I've learn is from try, try, try and try again.
But keep in mind I've made programs since 1986, (in many different program languish), so it is many years of expires.
The best advice I can give is, get an idea and try to get it to run - in first place use the Internet to solve problems - because you learn more that way, as get the answer served on a plate.

 

[RainLover]

Hi RainLover,
I did not try to make a subform. Perhaps it is better. I would like to know one thing. If I make the other form a subform, how can I filter out records so that only those are visible whose [radar_id] = [RaDAR_Id] etc.

This is the link to the database converted to version 2003

The Properties Dialogue Box for the Sub Form allows you to Enter the Master and Child Fields.

This is where you would make Radar_ID = to Radar_ID.

It should be simple to find examples through Google.

The other way is when you install the sub Form using the Toolbox Menu it should prompt you for that information.

I find the hardest bit selecting the Sub Form in design view. But I am sure you will work it out.

If you have a problem Tell me the name on the Master and Sub and I will download and do it for you.

 

[RainLover]

A sample Form and SubForm is attached.

It should open Automatically.

Try entering some data. No Code is required in this sample. frmRadar.

 

[RainLover]

Hi JHB,
I am really glad you made it work. Many thanks for fixing this. Just the last question, do you think the code did not work because of the issues you have just pointed out or was there something else?

Cheers!

EDIT: I feel like I need to buy and read a good textbook. There are many issues and traps I cannot learn from "try and error". Would you suggest something good? Having said that, I understand there is no the ultimate book that can give me everything and I am not looking for it but I'd like to have similar points to yours. Thanks.

There are lots of good books available and then there is the web.

What I believe every new person needs is a Tutorial rather than a book of facts etc. The a Dummies Book or the Idiot series. The name sounds insulting but they contain a lot of information. I still have my book on Access 97 which I may refer to from time to time.

 

[ZikO]

Hi,

I've looked at your database and got it to run. It is not the way I would do it, but you know your business best. The database is attached.

I've looked at the database again. You have mentioned here that it was not the way you would do it. Could you share your opinions here? I am sorry for this question but I am still learning programming and designing databases. I hope it is not a problem. Cheers!

@RainLover
Thanks for the link to this database version. I've looked at it and it looks all right. I may consider to choose this form but I need to take into account one thing or two. Thanks.

 

[RainLover]

Hi,
@RainLover
Thanks for the link to this database version. I've looked at it and it looks all right. I may consider to choose this form but I need to take into account one thing or two. Thanks.

I don't know what is going on in your head so that make things difficult.

The better you can describe your question, the further you go to actually solving it yourself. In other words the more you understand your problem the more adept your are to finding a solution.

If there is something going on that you can't describe perhaps if you grabbed a pic of someone else's Database that might help you explain.

I will give this piece of advice which you may appreciate more as time goes on.

"An excessive amount of Code is a True sign of a poorly designed database. "

Either that or less is best.

I showed some code to one of the AWF's moderators a couple of years ago. I thought it was pretty neat. He looked at it and reduced what I thought was a small amount of code, about 20 lines and reduced it to three lines.

Let me know if I can help.

 

[RainLover]

Hi,


I've looked at the database again. You have mentioned here that it was not the way you would do it. Could you share your opinions here? I am sorry for this question but I am still learning programming and designing databases. I hope it is not a problem. Cheers!

I should add that while I am suggesting something different to JHB I am not for one moment suggesting the he is wrong.

We all have our different ways of doing things, and we also have our different areas of expertise.

Cheers.

 

[JHB]

Hi,
I've looked at the database again. You have mentioned here that it was not the way you would do it. Could you share your opinions here? I am sorry for this question but I am still learning programming and designing databases. I hope it is not a problem. Cheers!

If I should keeping in the way you have done, then I would use an unbound form, and wait saving the new record/ the change until I click a "Save" button.

But with the small amount of controls for "details" and the one to one relationship you have here, I would keep it on the "Main" form. If you have had a one to many relationship I would have hold it in a subform.
I'm not a fan of clicking buttons "here" and "there" to get access to the data, it takes to long time and I don't find it user friendly.