No current record on form linked to query (2000)

Topic

New Reply

Hi All,

I have a form whose control source is query. I am attempting to open the form to find a name that is on a list on a different form. I have used code from this forum that has worked in the past. The only difference is the form that was being opened was linked to a table not a query.

My problem, when I doubleclick the name on the list used to open the form I get a message that says no current record. First I assumed it was because I had to somehow requery the query the form is linked to. (I’m not sure how to do this but it is where my thoughts went), however if I open the form on its own, it automatically opens to the first record in the query. Thus I am confused as to why the the message stating “no current record”. How do I get past this??

Thanks,
Leesha

Reply | Quote

Replies

How do you open the form, is it with DoCmd.OpenForm and a criteria?

Reply | Quote

Hi Pat

frmAccountName opens frmDemographics by double clicking a name in lstAllAcccountNames. The code is as folllows:

Private Sub lstAllAccountNames_AfterUpdate()
‘ Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst “[AccountID] = ” & Str(Me![lstAllAccountNames])
Me.Bookmark = rs.Bookmark

End Sub

Private Sub lstAllAccountNames_DblClick(Cancel As Integer)
DoCmd.OpenForm FormName:=”frmDemographics”, _
WhereCondition:=”AccountID = ” & Me.lstAllAccountNames

DoCmd.Close acForm, “frmAccountName”, acSaveYes

End Sub

The “on load” code in frmDemographics is:

[FullName] = [SALUTATION] & ” ” & [FirstName] & ” ” & [MiddleInitial] & ” ” & [LastName/BusinessName] & ” ” & [SUFFIX]

Me.AccountType.SetFocus

__________________________________________________________–

If I open frmDemographics by itself without using the list on frmAccountName, it opens to show the first record in the query. It is only when I open it from the list on frmAccountName that I get an error that says “no current record”.

Thanks,
Leesha

Reply | Quote

Hi Leesha,

Try commenting out the line [FullName] = … in the OnLoad of frmDemographics temporarily (insert ‘ before the line).
What happens now if you double click the list box? Does frmDemographics open with the correct record, or does it open on an empty record?

Note: acSaveYes in DoCmd.Close acForm, “frmAccountName”, acSaveYes means that you save the design of the form, not that you save the record (that will be done automatically by Access). Saving the design of a form will fail if more than one person uses the database.

Reply | Quote

Hi Hans,

I took out the line as you suggested and it opens fine, to the record I want. Of course now that leads to two questions. First, my need to understand the process (vs just copy code given to me) – why did this happen? When I think of the hours I spent trying to find the fix before posting and bothering you guys! Jeese!

Second question – since that line blows up the load process where would you suggest I put it so that the FullName box populates the way I need it to?

Finally, thanks for your tip re the form save. I had no idea! As always, I appreciate any and all help/tips you provide.

Leesha

Reply | Quote

Hans!!! I found a fix. I tried putting the code back in after the “me.AccountType.setfocus” code and it worked. I figured that if the code re the FullName loading first was blowing up because the “current record” hadn’t been found yet, possibly inserting it after the record had been found and the focus set to a source on the form would correct the problem!! Ask me if I’m tickled tee hee.

Thanks Again,
Leesha

Reply | Quote

I’m glad you found it yourself. One of the possibilities was indeed that the OnLoad event occurs too early to manipulate data.

Reply | Quote

I spoke too soon. I’m not sure why as I haven’t made any changes to the forms as listed previously and now I’m getting the “no Current Record” error again, withe following code highlighted Me.Bookmark = rs.Bookmark. I took everything out of the load event on frmDemographics, but still get the message. I’ve tried having frmDemographics already open and populated with an account in it to see if that was the issue, but I still get the no current record error. If I run the query that frmDemographics is bound to it shows the accounts. If I open frmDemographics on its own it shows the first account in the query. It’s only when I try to open it from frmAccountName. HELP?!

Totally confused,
Leesha

Reply | Quote

Is the highlighted instruction “Me.Bookmark = rs.Bookmark” the one from the lstAllAccountNames_AfterUpdate procedure in frmAccountName that you posted in an earlier reply, or is it in code belonging to frmDemographics?
If it is in frmAccountName, the problem is in that form. Apparently you clicked on a value in the list box that doesn’t correspond to an existing record.
If it is in frmDemographics, post the relevant code from that form, so that we can see the instruction in its context.

Reply | Quote

Now I’m really stumped. I had closed down the project and went on to something else (eye strain) and when I opened it up to look at the areas you suggested. It ran fine. Makes no sense. I guess I should’ve resorted to the first troubleshooting trick I tell people at my office to resort to when the computer/programs are nuts – shut down and reboot.

Thanks!

Alicia

Reply | Quote

I’m so frustrated. The two forms I mentioned above have been working fine. I’ve been fine tuning them to lock them down the user needs and now when I go to open a new record either I get an error message that states that record isn’t available, (when the command is on frmDemographics itself) or if I open frmDemographics from frmAccountName with the following code …. DoCmd.OpenForm “frmDemographics”, acNormal, , , acFormAdd
frm Demographics opens to the first record in the query. It makes no sense to me at all. I’ve read and re-read the code and can’t find anything that would override the code to open a new record I’ve attached the code to both sheets in case someone can see what its wrong.

The way it is supposed to work, the user clicks the command to open a new record, from frmAccountName. frmAccountDemographics should open to a new form.

Thanks,
Alicia

Reply | Quote

You’re getting the “No current record error” in frmAccountName, not in frmDemographics. When you double click a list box, the On Click event occurs first, then the On DblClick event. After all, you click once first, then immediately afterwards a second time. The OnClick event tries to move the form to the record whose AccountID is equal to the one selected in the list box. I assume that something goes wrong there – can the list box contain AccountID’s that are nor displayed in frmAccountName?

About the other problem: if the record source of frmDemographics doesn’t allow adding new records, or if frmDemographics has its RecordsetType set to Snapshot, the form can’t be opened on a new record, so the first existing record will be displayed.

What is frmAccountDemographics? I see no mention of it in your code.

Reply | Quote

Hi Hans,

Thanks so much for your quick response. I was hoping you’d be around.
First, frmAccountDemographics was a typo on my part. It should’ve read frmDemographics.

To clarify, when I double click the list box on frmAccountName it opens to the correct record in frmDemographics. There are no problems there. The problem comes when I click the control button on frmAccountName called cmdAddNewName. frmDemographics opens, but to the first record in the query that frmDemographics is bound to, not to a new record. It was working fine. I’m not sure what happened.

The RecordsetType on frmDemographics is set to dynaset not snapshot.

Alicia

Reply | Quote

If you open frmDemographics directly, can you add a new record?

Reply | Quote

Nope. I’m “trying” to think like you and figured that would be a suggestion so I put a command on the frmDemographics to add a new record and get the same error message re not being able to go to that record.

Leesha

Reply | Quote

I would guess that the record source of frmDemographics does not allow the addition of new records. I assume that this record source is a query. If you open the query directly, is the “new record” button in the navigation buttons and in the toolbar greayed out? Is there an empty new record at the end of the records?

Reply | Quote

OK, chalk this up to another new learning experience for me. You are correct with all assumptions re the query. So, now how do I fix it to allow new records? I checked the recordset property and it is also dynasat and there are no locks on the records. I don’t recall making any changes that would’ve affecting this. If I did it certainly wasn’t intentional as I didn’t realize I could make these changes to a query.

Thanks,
Leesha

Reply | Quote

Can you post the SQL of the query that acts as record source of frmDemographics? To do so, open the query in design view, then select View | SQL. Copy the SQL text you see to the clipboard, and paste it into a reply.

Reply | Quote

I was just about to do that and then thought I’d just redo the query first to see if maybe one of the joins was wrong. I had added a new tbl to the query and if I’m correct the issue started after that. I’ve lost track. So I redid the query, checked the joins and it now allows me to add a record. Of course it is only by luck that I got it to work since I don’t really “get” joins and just try them till I get the data I want, thus what I did this time. Someday maybe I’ll find the error by reading the SQL the way you do. Anyway, I’ve learned something new.

Thanks again!

Leesha

Reply | Quote

OK, so now I’m beside myself. I started entering data into the database and got an error message related to relationships between the tables and duplicate data. I went and changed the relationships and primary keys and that appeared to correct the error however it blwew up my query again – in that I can’t add a new record. I recreated the query and now I get no records at all. There are 6 tables in query. When I set up the first two tables, there are no issues. However when I add the third table and run the query there are no records available at all. It doesn’t seem to matter in which order I add the tables, it happens every time I add a third table. When I tried changing the joins around not all of the data is obtained so that didn’t help, and still there were issues with more than three tables in the query.

I’ve attached the SQL. I hope you can figure out the my problem as I certainly can’t. I’ve been at this for 2 hours since my last post to you and I’m nuts!

Thanks,
Leesha

Reply | Quote

Why have you got 6 tables in the query?

From what I can see you only need 2 tables, tblDemographics & tblAlternateAddress.

Reply | Quote

I need all of the fields to return data. That is my problem. I’ve tried doing the joins in a variety of ways and no matter what the combination is, something doesn’t get returned or nothing gets returned or worse yet, I am unable to add new records.

A short history, my tables always tend to be very big as I’ve put all info into one or two tables. Everything I read on Access (I really do try to figure this out on my own) says to set up small multiple tables that collect specific data, that have a common link. That is what I tried to do here. The main table is tblDemographics. AccountID which is an automumber and is the unique key in that table. It represents the account name. Each of the other tables has a unique key called “autoumber” (so as not to comfuse myself) and then there is a “__ID” key (for example “referralID”, “BusinessID” etc.) which all join back to “AccountID” in the tblDemographics.

To compound matters (or mistakes), rather than use the lookup wizard to connect the tables as I normally do (but am trying to avoid based on input from this forum) I set up the relations “by hand”. I think I set them up correctly but now wonder.

Would I have been better of simply setting up one to two large tables???? I can go back and correct for that. If smaller tables is the way to go than how do I get them ALL to join in the query. The reason this query is soooooo large is the form it connects to is a tabbed form that allows the user to enter all the required data on that account, without opening multiple forms and without having multiple records per account. There should only be one record per account.

Thanks,
Leesha

Reply | Quote

I’m not sure I completely understand your description. Can tblAlternateAddress contain more than one record linked to the same AccountID in tblDemographics?

If the answer is yes, you have a one-to-many relationship between tblDemographics and tblAlternateAddress. You would be better off using tblDemographics as record source of frmDemographics instead of the query, with a subform for tblAlternateAddress (on a tabbed page.)

If the answer is no, you have a one-to-one relationship between tblDemographics and tblAlternateAddress. Unless the records become too large, it would be better to put all the fields in tblDemographics.

The same holds for the other tables (tblAlternateBillingSource etc.): if there is a one-to-many relationship, use a subform; if there is a one-to-one relationship, put all fields in tblDemographics unless the records become too large.

Reply | Quote

Hi Hans,

There would only be one record in each of the tables that correspond back to tblDemographics. Therefore, if I understand you right, I’d better off putting all the fields in one tblDemographics. If that is the case, is that the determining factor in whether to create multiple linked tables or one big table. Also, re subforms, I’ve never had much luck usig them and always find that they don’t look very attractive on the screen. It’s probably just me.

Leesha

Reply | Quote

Hello Leesha,

If there is only one record in each of the auxiliary tables corresponding to a record in tblDemographics, you’re better off putting all fields in tblDemographics. The only limitation is that a single record can contain a maximum of 2,000 bytes (excluding Memo and OLE fields).

About subforms: you can make them look any way you want, and for some data structures they are the natural way to display the data. But if you put all your fields into tblDemographics, you won’t need them, so you don’t have to worry about them.

Reply | Quote

Hi Hans,

As always thanks for the help. I’m going to resort to putting everything in tblDemogrpahics. Seems to be the easiest way to go. Re subforms – it must be me but I “don’t get them”. Gonna need to study up on those. I think I’ve bugged you guys enough for a couple days!!

Adios,
Leesha

Reply | Quote

I didn’t reply to this post because I’m in lala land at this time. Hans fixed you up as usual.

Reply | Quote

NP!!! I appreciate any help no matter what the timing.

Leesha

Reply | Quote

As Pat has already remarked, you only return fields from tblDemographics and tblAlternateAdress in the query, so including tblAlternateBillingSource, tblBusinessInfo, tblReferralDemographics and tblSpouse/SignificantOther seems unnecessary.

And for the query to be updatable, there must be a unique key on AccountID in tblDemographics. If there isn’t, a query joining tblDemographics to other tables on AccountID is never going to be updatable.

Reply | Quote

Hi Hans,

I just responded to Pat above. I NEED to return fields from all the tables in the query. Is that even possible. RE a unique key on AccountID – there is one.

Thanks,
Leesha

Reply | Quote