Working with a Double Query on a a Form

[June7]

If you want to regenerate the MeetingID primary keys then yes, of course delete from tblMeetings. Why would you even consider deleting the autonumber field from tblPersonnel?

However, if these values have already been used as foreign key in other tables, the records may no longer be properly related. For instance, the meeting record that currently has ID 4 might not have ID 4 when you create a new autonumber field in tblMeetings. However, since the only other field in tblMeetings is a date/time, the ID will probably generate the same order except for ID 14 will become ID 13.

It is possible to run an INSERT sql action and insert a record for the missing ID 13. No need to remove relationships.

CurrentDb.Execute "INSERT INTO tblMeetings(ysnMeetingID) Values(13)"



Be sure to run Compact & Repair immediately after doing that, otherwise the next new record in tblMeetings will generate ID 14, not 15, and you will have duplicate, in spite of the Indexed Yes(No Duplicates) setting on that field.

As I said, fixing gaps is tricky.

[Lou_Reed]

I have so few records that I think that just by inspecting them, I can determine if everything is okay. Of course, these are bogus phony-baloney records, so I am not sure when we start adding
real stuff to the db what will happen to autonumber then?

I do have a problem that when I open the db, I get an error "The expression you entered has a function name that Microsoft Access cannot find".

I believe it must be in the Division Dashboard (the topmost) form since that one opens up when the db is opened. In this error case it only opens after some futzing and the only half-__ed.

I also believe that it must be related to the change in the forward button on frmEmail to Forward. My typo.

Anyway something is wrong here and it is necessary to know what is causing the error that I mentioned above.

It simply should go to Division Dashboard when the db is opened.

Why doesn't it?

Respectfully,

Lou Reed

[June7]

In 'real' db, ignore the sequence gap of autonumber, don't display to users.

Well, I can't find anything anywhere else that depends on the 'Foward' spelling.

Macros are just harder to debug.

I have never liked and never used Switchboard form. There is a reason MS has pushed it to the background and made harder to even find the wizard for this, it's just not a friendly critter.

[Lou_Reed]

Okay, so how do I rename foward to forward? It seems easy enough, but I am thinking that it will reverberate or propagate through the system if I am not careful. It is not as easy as renaming foward to forward!

No telling where this goes. Could this be a job for V-Tools?

Any help appreciated.

Thanks in advance.

Respectfully,

Lou Reed

[June7]

I've never used V-Tools so I can only say possibly. Rick Fisher's Find and Replace could.

I went to earlier version of your db. I changed Foward to Forward. This does not cause an issue with the Dashboard form - it opens just fine. So something else has been changed and it involves the AutoExec macro. It is trying to call and run VBA custom functions. However, there is no VBA general modules in that db.

[Homegrownandy]

Auto numbers, once used are used. If that record is deleted that number is gone. I believe its possible to edit the directory of the database but its really not necessary. The auto number field usually is only used internally in your system. Not by the user.

[Lou_Reed]

I wish it was just as easy a changing a name. It is not. That is where V-Tools comes in handy. It will find every instance of a word say Foward and if the users wants change it to Forward - every one. Trusting memory
and inspection capabilities is not the way. I am not sure what caused the macro to crash. It just did. So now I must go back and change with a viable working copy.

Hopefully, I will not repeat the error again. It is probably some sublime error that I unsuspectingly made.

Thanks for your input. I certainly needed it.

Respectfully,

Lou Reed

[Lou_Reed]

The file attached contains a db that is mostly clean of the errors that I accumulated yesterday. This file is almost like the one that started failing yesterday, but with no errors.

It does have one and that is in frmEmail, clicking on the next and previous buttons, will not move through the MeetingDates and MeetingID's as it should. The frmEmail has the data entry button property turned off which I know its what it must be. So dataentry is no.

The next and previous buttons have macros associated with them and they should work. They work clumsily, but they should work.

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[Lou_Reed]

I think that I know why the frmEmail is not moving through the meeting dates even with the form properties dataentry key set to no. The frmAttendance seems to have
nothing in the MeetingID column. The are four meeting and their dates, but no MeetingID.

I started to put them in in the table (I know this is not the correct way, but I just want it to have those numbers now). I put all of them in and when it came
time to click away the tblAttendance it said " You cannot save tblAttendance at this time. So I only put in three MeetingID's and there are four meetings, when I tried to put in
the fourth MeetingID opening tblAttendance up again it simply let me put the number and would not let me save the table. So the table is incomplete. I cannot put anything else in it.

Why am I not allowed to save the table once I put data in by hand?

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[Lou_Reed]

I have made a grievous error. I uploaded the wrong file.

Here is the file that I have questions on. This is the file that I refer in post # 54. It is attached to this post.

Sorry about that.

Respectfully,

Lou Reed

[Lou_Reed]

Okay, I got this one. I cannot add a MeetingID no. 4 because there is none in tblMeeting. So do I add in tblMeeting first, and then tblAttendance?

That would seem logical.

Respectfully,

Lou Reed

[June7]

Believe I mentioned many posts ago (and more than once) that there was no data in the ysnMeetingID field of tblAttendance.

Yes, record must be in tblMeetings first if you want to enforce referential integrity. Set combobox LimitToList to Yes. However, new records can be added to tblMeetings 'on-the-fly' during data entry. Requires VBA code in the combobox NotInList event.

Even without ysnMeetingID values in tblAttendance, frmEmail will still move through records. Just appears not to be moving because most of controls are blank. Can demonstrate by removing data from tblAttendance ysnMeetingID and add a textbox for PersonnelID and you will see data change as you move between records. And, again, would not really want to display these IDs to users in final design. It does seem odd that the Employee combobox does not reflect name associated with PersonnelID - I have not closely examined frmEmail RecordSource query.

I see you still have the embedded macros for the Previous and Next buttons (renamed as btnPrevious and btnNext). Clever use of the macro error handler to eliminate the popup error message when hitting EOF and BOF. The VBA could be modified to do the same thing but think I will have a textbox to serve instead of popup to notify user they have hit the first or last record by sitting visibility of textbox and also continue to disable/enable buttons. Users tend to keep clicking button not aware they have reached the first or last record.

You keep changing the RecordSource of frmEmail. I am still not sure of purpose for this form. Do you want to use it to edit data and/or add new records to a table? Which table?

[Lou_Reed]

I just want to ask when you convert a macro to VBA, at the end of the conversion you have the converted VBA code and the macro. How can you save VBA code and dump macro?

I changed the record source of frmEmail because I found a way to do it in one query, not two. Then I went back to two queries. I do not know why. It seems either works.

I will pick most direct.

I have programmed a lot in my career and I find the VBA code syntax rather tedious and varied. Hence, I use macros. They can always be converted to VBA code. But what do you do then? You have VBA code and a macro? See above statement.

do you need to allow more records entry?

This was stated by you in previous post. The answer here is no, but I guess if it were yes, I need to change form data entry property to yes?

I appreciate all your help. Was very down yesterday at quitting time, but spirit's rose when I realized that I had a backup of the db, that worked and all I had to do was update the file.

It saved time and took time.

If I want to use your VBA code for next and previous buttons, but a macros is there now - what do I do? I guess that I could click buttons away and start anew, but that seems
awfully long for such a task. So what do I do?

Any help appreciated. Thanks I advance.

Respectfully,

Lou Reed

[June7]

Select [Event Procedure] in place of [Embedded Macro] in the event property. As far as I know, the embedded macro will just cease to exist. If you then remove [Event Procedure] and go back to [Embedded Macro] you will see the macro is empty and will have to be rebuilt. If you use the 'Convert Form's Macros to VBA' wizard will be the same outcome. This utility can be found on the Design tab in the Tools group. However, if you are happy with macros then keep them, after all, the buttons are working.

[Lou_Reed]

The buttons are working. When I go through a meetingUD equence, they do not go in order, 1,2,3,4, or 4,3,2,1. Maybe it is because they go in the order of the tblAttendance?

Also, when they get to the end (either 1 or 4) things go crazy if yiu keep going in that direction. Can this be fixed and still keep the macro?

If possible that is what I want.

Respectfully,

Lou Reed