Working with a Double Query on a a Form

[Homegrownandy]

I'm working on something similar so I've made this to try and help. This query shows the persons name. It then shows the expiry date for any work permit cards. Lastly it shows the name of that card.



just note that the Junction table will contain just numbers and dates in my case.

[Lou_Reed]

Okay, I am going to follow my intuition and your advice and take it out. When I first posed this question, I used the argument that you are using now. It just seemed unnecessary. However, in all the posts in this thread I came away after reading yours thinking that it was okay. I then changed the junction table and now i learn that it is not okay. That is what i expected.

Now, of course, a db can have numbers in a table. It should. Also, the designer of the db can set an algorithm (DLookup for example) that will convert that number into info. That makes for inefficient db does it not?

I will do as you say and as I initially thought. It just seems better.

Respectfully,

Lou Ree

[Homegrownandy]

you don't need any look ups if your relationships are correct.
My query example will show all the data related to that person and card with no lookups.

[Lou_Reed]

In the attached file I have my current db as it stands now. I have put some items in the tables (by hand, I know that I should not do that, but it helps) so I can work some of the queries.

I have 14 meetings, four employees. In the junction table, I have the results. Should be 52 (4*13). I mean everyone is eligible to receive a invite to every meeting. Whether it is sent and whether the person attends is another story. But there should be 56 combinations. The number of meeting times the number of employees.

I do not know if I did it correctly. That is why I attached the file that contains the current db.

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[June7]

If you want tblAttendance to show all possible combinations, then you need to enter 52 records into tblAttendance - this is called data entry work. And what form/subform arrangements are useful for - entering records for many-to-many relationship. I presume tblAttendance purpose is to show actual attendance info, not possibilities - enter record when appropriate.

If you want a query to show all possible combinations:

SELECT tblMeetings.*, tblPersonnel.* FROM tblMeetings, tblPersonnel;

Note the lack of JOIN clause, this causes every record in each table to associate with every record of other table - a Cartesian relationship. There will be 52 records. This will not be an updatable query - cannot edit the data.

[Lou_Reed]

If you want tblAttendance to show all possible combinations, then you need to enter 52 records into tblAttendance - this is called data entry work. And what form/subform arrangements are useful for - entering records for many-to-many relationship. I presume tblAttendance purpose is to show actual attendance info, not possibilities - enter record when appropriate.

I understand that putting 13 * 4 = 52, entries seems a monumental exercise in extreme busywork. I know that. But you have to start somewhere and it seems better to have too many entries and not need them than to have too few entries and need more. This is called beginning programing for an MS Access 2010 newbie.

I was going in that direction and then to start finding ways to eliminate unneeded entries. In a earlier post I said than one could assume that if one's name was missing from the attendance list then he neither got an invitation nor did he attend the meeting. Obviously, not every employee will be invited to every meeting. That would be absurd. But you have to start somewhere and I thought that was the ideal place to start.

If the junction table just lists all of the possibilities of combinations with no restriction on who is invited or who attended that is called Cartesian.

So from that we could just whittle it down. Seemed reasonable at the time.

I also have any issue with the db and the data entry yes command.

I will post it when I understand more of what is going on.

Thanks for your input and patience, but please understand my time in programming and design Access db is measured in months, and in the single digits at that.

I wish that I could understand all of your replies with a single post, but at times it just is not possible. Hence, I must ask questions again.


Respectfully,,


Lou Reed

[Lou_Reed]

I am attaching a file that contains my most current db. In it I changed on frmEmail, the data entry option from yes to no. I am also including Snagit screenshots of frmEmail showing both before and after the parameter change.

I do not even see the form frmEmail after I switch the data entry from yes to no and open frmEmail in form view

I am sure that this is not what is supposed to happen.

I clearly have left something out after I performed the other step and put data in my junction table for Meeting and Personnel in my db.

The I changed the data entry parameter and here is the result.

What is wrong?

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[Lou_Reed]

Okay, I have got this one. The qryPersonnelMeeting1 was missing an AttendanceID which made frmEmail to have to ask for it when I opened it in form view.

Thanks for your help, but this is the correct answer and I need go no further on this.

Thanks for your help, anyway.

Respectfully,

Lou Reed

[Lou_Reed]

This change of the data entry option on frmEmail worked. However, when I go through the meetings forward everything is fine. If I start out by going back (previous), well that is another story. I get the

"You can't go to the specified record"

error.

I am not sure the cause of the error. On the internet it says something like you failed to save a record. It gives a VBA Access command like
DoCMD, ,acNewRec


It does not say where to place it and in my case it will not help because in my db the previous and next command are MACROS. I guess that I could convert the MACROS to VBA code and then put in the DoCMD, ,acNewRec, but that seems a long ay around to get rid of this error.

An easier way exist?

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[Lou_Reed]

In the immediate Post #39 above, the db has an additional problem when going through the data and it is not just with "You can't go to the specified record"

For some reason it seems to be having a lot of problems with MeetingID = 3. Going through the meeting and it will 1,2 and 4, skipping 3, and if that is reversed
it will again skip MeetingID on the down count. I am not sure what the issue is since, the results of the query that is the record source for frmEmail is
showing all four of the queries. So why is it chocking on MeetingID = 3.

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[June7]

You get that message because already on the first record and there is nowhere to go when clicking Previous, there is no more 'previous record'. So you need to first check if on first record and likewise for the Next check if on last. Here is VBA code:

Private Sub Back_Click()
Me.RecordsetClone.MovePrevious
If Not Me.RecordsetClone.BOF Then
    DoCmd.GoToRecord acForm, Me.Name, acPrevious
Else
    Me.RecordsetClone.MoveFirst
    MsgBox "First record."
    Me.Back.Enabled = False
End If
Me.Foward.Enabled = True
End Sub

Private Sub Foward_Click()
Me.RecordsetClone.MoveNext
If Not Me.RecordsetClone.EOF Then
    DoCmd.GoToRecord acForm, Me.Name, acNext
Else
    Me.RecordsetClone.MoveLast
    MsgBox "Last record."
    Me.Foward.Enabled = False
End If
Me.Back.Enabled = True
End Sub

You have misspelled "Forward" as "Foward" in the button name so therefore is misspelled in code. Might want to correct.

This code will not allow moving to new record row. Do you need to allow new record entry?

Not sure embedded macro can accomplish this. Why use macros? I never use macros, only VBA. If not a web-based database, stick with VBA.

Not skipping meeting 3 for me. I see all 4 records on frmEmail. Why two textboxes displaying MeetingID?

[Lou_Reed]

So I take it I must translate macros to VBA before doing all the rest. Okay, I will do. I use macros because of their directness. The (MS Access 2010) puts it together with no fuss, no muss. With VBA you have a
lot of pain on syntax - not so with macros; that is coming from a experienced Excel VBA programmer (although I have not do any Excel VBA programing recently).

If I want to and now I want to, I can always translate to VBA.

My spellchecker did not catch forward. I cannot explain that. Must be a very recent error.

I also want meeting table to renumber. It is missing no. 13. I can change the autonumber back to one on tblMeetings. But what all the controls that depend on tblMeetings?

I assume that must be done manually or does it propagate them through the db.

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[June7]

Don't know why Excel VBA programmer would have that viewpoint. 'Macros' in Excel are really VBA procedures. The Excel macro recorder just creates a VBA procedure but the code syntax looks different. I use the recorder to generate some code to get an idea of how to do something but usually have to modify it to accomplish what I want. That requires knowing VBA. Whereas the macro builder in Access does not generate VBA procedure and no knowledge of VBA is required.

Yes, Access macros may be more 'user friendly' but have less versatility. I doubt a macro can fully replicate the VBA I posted. But perhaps the macro error handler can be modified to give the message provided by the VBA code.

With an autonumber field as PK, sequence gaps should not be a concern. 'Fixing' sequence gaps is tricky and probably not worth the effort. Ignore the gaps. Autonumber PK/FK not supposed to have any meaning to users, and therefore are usually not displayed. Autonumber PK/FK is a tool to link related data but users should not rely on them to have any meaning. The meeting date is of value to user. Review http://www.databasedev.co.uk/autonumber.html

[Lou_Reed]

In the db that is contained in the attached file, I am trying to restart from 1 the autonumber (the first field) on tblMeetings. I have done this many times on other tables. I open tblMeetings in design view and click on the autonumber field.

I then try to delete it and it says that you cannot delete MeetingID (the official name of the field) because it is part of a relationship. Well, I remember that from before and in the relationships table I delete the relationship between tblMeetings and tblAttendance.

Then I again try to delete and it still will not let me delete because it is part of a relationship and so on.

The relationship has been deleted and it still will let me delete the MeetingID field.

MS access should allow me to delete this field after I remove the MeetingID field since the relationship between tblMeeting and tblAttendance is now been removed.

What step am I leaving out?

Respectfully,

Lou Reed

[Lou_Reed]

I think that I see my error. Please advise. I deleted the autonumber field from the table that I spotted the missing number on : tblMeetingID. Maybe I should have deleted it from the main table as it says on some of the instructions on the internet, that are discussing how to do this.

So I deleted autonumber from tblMeetings instead of tblPersonnel. I am not sure if this is the answer, but it seems logical. As I said when I did this before it was quite easy. I am at a lost to determine why it is
so convoluted now. That I is the purpose of this post. Is the correct way to delete the autonumber from the main table, i.e.. to start from there?

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed


BTW I have never used Macros in Excel, only VBA and it was quite easy. Only math function as I said.

Anyway, sometimes I look at a table and say well their a N number of items. Then check other parts of the db and
notice that they have say one less or N-1 items. Then I tediously (very tediously) check the whole list as is my nature. I then discover after
much checking that list that I thought has N items, actually had N-1 items, because an item was deleted and the autonumber field was not reset.

I know that it is usually advised not to renumber or reset etc auotnumber. But it was so easy to do before that I could not resist. I tried it again and it was not easy
or direct. hence, the post.

I hate loose ends.