Solved

Access 2003 Database form help

Posted on 2010-09-22
17
289 Views
Last Modified: 2012-05-10
Ok I am back with my call log database and my manager would like some changes (go figure), so basically he wants a call view form and a search form added.

The main form (frmCallList) lists all calls for the day and has a option to view by shift. But it only shows the information from the call table (tblCalls). What the want is a button to open the selected call in the list in another form but displays all details associated to that call in a list form. I have dubbed this frmCallView. I tried using a query but still learning this I don’t know how to separate the details in the query to the list box or if I am even on the right track.

The search form is basically a keyword search (never done this before) on the calls table and details table. It will have a search button and a list of calls not details will display in a list box (yes they have a thing for list boxes). There should also be a view call button like the one I talked about earlier (I am assuming it’s the same code). There is a high probability of needing it to exporting the results to a report or file but with the details.

I am concerned I do not have this set up correctly (I know the New Call button doesn’t work right). I can tell you I don’t like the form calls and how we currently have to update a call. I was thinking that there should be two forms; one for new calls where one enter the new call and the first detail for the call, and one for updating the call where one is adding a new detail to the call but the detail is still associated to that call. I don’t know; I am open to suggestions.

This was supposed to be simple.

I have attached the file.
 CallCentre.mdb
0
Comment
Question by:Everwulf
  • 8
  • 8
17 Comments
 
LVL 16

Expert Comment

by:Sheils
ID: 33733658
Check out the code in the double click event of the list box in frmCallList
CallCentre.mdb
0
 
LVL 20

Expert Comment

by:clarkscott
ID: 33733739
Here's an example of what I do.  New form "frmSearch_new"
Only added 3 fields for search.  You can add more.  Code in the CONTINUE button.

TITLE is a wild card entry.
Call date Start and STop (date range)
combo category

enter any criteria you wish - none will display all records.
I changed your qrycall query to allow ALL calls and only details that match.

The RESET button clears search criteria.

Scott C
CallCentre.mdb
0
 

Author Comment

by:Everwulf
ID: 33763437
Both of you are on the Right track but I know I didn't explain it very well. I have the forms designed the way they were hoping for.

For the Search form:
The search form has a list field that will list the calls that contains the keyword being search for. There are two tables that make up a call the Call table and Detail table. I set up a relationship between the two because a call will have multiple details. But they only want the main call listed. The list should be by date then time and show the call title.

For the Call View Form:
We need the details for that call in the list box so we can see all that has happened. We also added a button to add more details. This button should open the calls form to the specific Call but the details area should be on a new record for updating. This should be similar to the update button on the Call list (which doesn’t work correctly) form. Details should be listed by time but there are times when a call will cross over from one day to the next. So that will need to be taken into account.

For the Call list form:
Basically the buttons. For new call the button should open the Calls form to a new call record and a new Detail record. The Update call button should open the selected call to the Calls form showing the call selected but the detail section should be for a new record.
View Call should just open the selected call in the Call View form and be the correct call and list all details.

I can get the buttons to open forms I am just not very good incoroacting list boxes to do what they need to.

I have add the database again this time with the forms changed to the way they wanted it to look.
CallCentre.mdb
0
 
LVL 16

Expert Comment

by:Sheils
ID: 33765261



So all you need is the code to open a for based on a list box select. If so use the following syntax

DoCmd.OpenForm "FormName", , , "FieldName=" & ListboxName.Column(ColumnNumber)

FormName is the name of the form you want to open
FieldName is the field you want to use to filter the form that is being opened
ListboxName is the name of the listbox
ColumnNumber is the column in the listbox that contain the value that will be used to filter the form
It is important to remember that the column number is zero base. So
1st column is ListboxName.Column(0)
2nd column is ListboxName.Column(1)
3rd column is ListboxName.Column(2)
and so on ....

To refer back to you DB you will notice that in my demo I use the following code to open frmCallView from the double click event of the listbox.

DoCmd.OpenForm "frmCallView", , , "idIssue=" & lstResults.Column(0)

You can use the same code on the onclick event of a button.

NB: You NEED to include idIssue in you qryCalls for this to work


0
 

Author Comment

by:Everwulf
ID: 33767286
Thanks that helped with the View Call button but I still need the View call for to look right. It opens the Call in the correct form (frmCallView) but I can’t seem to get the list box in the form to display the Details associated with the call that was opened.

There is also the problem with the ‘New Call’ and ‘Update Call’ button to work right. I can get them both it to open the right form but not to a new record. The new call show open to the form and it be a new Call and New Detail and the update call should open the call selected but to a new detail record.

If you look at the frmCalls form you will see how we have it set up. We could add the navigation button back but we only want to use this form for information entry and not record viewing. That is what the call view form is for.

There is also the search form (frmSearch) I have no Idea how to code.
0
 

Author Comment

by:Everwulf
ID: 33767636
Good news I fixed the New Call and Update Call buttons. Now if I can get the list box in the frmCallView to display the call details and get the frmSearch form to work I will happy.
0
 
LVL 16

Expert Comment

by:Sheils
ID: 33767970
All the buttons are working in the attached. I have also added some error handler.

As for the search form I suggest you ask that in a separate question because it is a separate topic. Having said that I will point you to a few article that may help you setting up a search form:

http://www.fontstuff.com/access/acctut17.htm
http://www.databasedev.co.uk/text_search.html
http://www.databasedev.co.uk/list_box_searching.html

Cheers


SB
CallCentre--1-.mdb
0
 
LVL 16

Expert Comment

by:Sheils
ID: 33767990
Also note. I do not advise using a button to search listbox because if the user does not select anything in the click the onclick event of the button will return an error, unless you have an error handler. It best to use the double click event of the listbox and just insert a label to inform the user that they can see details of a call by double clicking on it. Button works best with unbounded textbox.
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:Everwulf
ID: 33768747
Thank you, I will look at those links. For the searching though the list box is for listing the Calls that have the word being searched for. The text box is the field to use the word to search by. The list box should take into account the Call Table and the Detail table but only list the call and only once. But then once there is a list of calls I need to find a way to export the calls to a report. (Your right this might be a better if it was its own issue.)

Can you take a look at the frmCallView? I can seem to get the list of details for a call to show in the listbox lstDetails. Other than that thank you very much and I like the error handling for the buttons.
0
 
LVL 16

Accepted Solution

by:
Sheils earned 500 total points
ID: 33772670
You did not have details for every call that's why some of them were not showing up in frmCallView which is based on an inner join qry (qryCalls). I have changed the query, see attached.

As regards asking question on EE it is best to ask for help on one issue at a time. It helps simplify you question and the experts can quickly understand what you want and provide an accurate answer.

So the current question should have been splitted into at least 6 separate questions:

1) How to create search form using unbounded textbox?
2) How to export a report to ....... using a button on a form?
3) How to stop duplicate calls in a listbox?
4) How to open a form to enter new records using a button on a form?
5) How to open a form to edit a record selected in a listbox using a button on the form?
6) How to open a form to view a record selected from a listbox on a form using a button on that form?

Cheers


CallCentre--1-.mdb
0
 

Author Comment

by:Everwulf
ID: 33775920
Ok I see what you did with the query. Thanks. Also thank you on the advice for EE. I think I only have one more issue and then I can close this.

Issue one:
From the call view form the listbox for details is showing all details and not specific to the call selected. How do I code the listbox to only show the detaill related to the call?
CallCentre.mdb
0
 
LVL 16

Expert Comment

by:Sheils
ID: 33776845
The easiest thing is to replace the listbox by a subform. There are in-build functions in access that will help you link the form to the subform. You can make the subform look like a listbox if you want by setting it as a continuous form, deleting all the labels, lining up all the text boxes in a row and making the subform borders transparent. No code required.

If you insist on using code with list box then use the form onopen event to set the listbox recordsource.

Something like

Private Sub Form_Open(Cancel As Integer)

Me.ListBoxName.RowSource = your sql where somefield = " & Me.Somefield

End Sub

0
 

Author Comment

by:Everwulf
ID: 33777323
I have the subform on ther and it is working a you said but how would I set the subform to sort by date then time. The issue is we are try to make sure if records are add out of sequence that the show in sequence. We thought the way to do that would be with a listbox.
0
 

Author Comment

by:Everwulf
ID: 33777389
I also looked at the code for the listbox, and could you define what "Your sql" means. Sorry but I am still pretty new at database development
0
 
LVL 16

Expert Comment

by:Sheils
ID: 33782543
To sort the subform refer to this article:

http://support.microsoft.com/kb/304255

Your sql is the sequal for the query used as the listbox rowsource. To learn more about sql check this site:

http://www.w3schools.com/sql/default.asp

Cheers
0
 

Author Closing Comment

by:Everwulf
ID: 33795741
While the solution was correct, I still am having problems getting it displayed right. I have mentioned I am new to database development and I appreciate the help a lot of the responses seemed to hint at what to fix and figure it out yourself. I understand the need to learn it myself but if I don't know that code or right way of doing it directing my to a website to teach me is not was I feel is a good solution. Showing me the right way and add a link that says for more information go here would have been more appropriate. Basicly I am closing the ticket hoping I will be able to figure it out based on what I have been told thus far.
0
 
LVL 16

Expert Comment

by:Sheils
ID: 33796940
Everwulf

I understand that you are new which also implies that you do not know how to correctly ask a question. It was very hard answering you question because there was no specific question to start of with and you kept asking new questions throughout the post.

So I think that your grading was greatly unfair but I am not going to take that against you. However, I will give you some advice. In future question please be specific and stick to one issue. For example, if you want to know how to use a button to open a form just ask:

How to I use a button to open a form. If the form is not displayed correctly when it opens that's another question which is However do I get the form to display xyz when I open it from a button.

Then if you want to use that form as a search form that is yet another question.

How to right sql that's another question too.

You will get the answer for all your questions here but you need to ask them one at a time.

Cheers

SB9
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now