Help Needed with Combo box

[Crabbymcdo]

Hi All

Need some help, I've not used Access for a few years and I am struggling to program a combo box.

I am creating a call logging system to log any incidents in work, I have pretty much sorted most items out but my main problem is as follows:

I have a form fronted design. Where the user chooses to either log a call or close a call.

The section im having a problem with is my call closure form (frmcallloggedpage)

I have a table (tblmain) that records all the logged calls when they are put on to the system, the form contains text boxes that link to the table to bring up the details, however I have a combo box on the form that I want to use to select a specific record.

I have no idea where to start on this one and its starting to annoy, ive looked at a couple of places online, including here, but so far no joy,

Please help – any more details needed please let me know

Thanks in advance

CrabbyMcDo

 

[pr2-eugin]

CrabbyMcDo, Welcome to AWF..

What you need to do is create an Unbound ComboBox, if you use the wizard, you do not even have to worry about any fancy coding.. But if it interests you, Allen Brown has a sample coding.. Check it out.. Post back if you are stuck..

 

[Crabbymcdo]

Hi pr2-eugin, thanks for the speedy response

I have seen this method using the wizard several times online but never able to get it to work, I have just tried it again, but again nothing, not sure what I doing wrong, i would upload the file for you to see but it is 25mb

 

[pr2-eugin]

Hmm, some more links..
http://www.vb123.com.au/toolbox/07_access/findrecord.htm
http://support.microsoft.com/kb/287658
http://ms-access-tips.blogspot.co.uk/2011/05/using-combo-box-to-search-for-record.html

To create a Sample DB (to be uploaded for other users to examine)..

1. Create a backup of the file, before you proceed..
2. Delete all forms/Queries/Reports that are not in Question (except the ones that are inter-related)
3. Delete auxiliary tables (that are hanging loose with no relationships).
4. If your table has 100,000 records, delete 99,990 records.
5. Perform a 'Compact & Repair' it would have brought the Size down to measly KBs..
6. (If your Post count is Less than 10 ZIP the file and) Upload it..

 

[Crabbymcdo]

Please see attached the database

 

[pr2-eugin]

Right I did not know that you are using a older version of Access..

The feature to find a Record from ComboBox, built in code was introduced in recent versions I believe..

For this you have to use a bit of coding.. I have edited the code.. See if this helps..

 

[Crabbymcdo]

perfect that's that bit done,
perplexing thing though now is that now the close button does not work
getting an error code of 3127 stating an issue with my SQL,

what im looking for it to do is to only move the selected record to tblout with the closing comments

 

[pr2-eugin]

Sorry I do not understand what you mean.. I thought you wanted to go to a specific record when you select from the ComboBox.. There was nothing about Moving it to another table??

 

[Crabbymcdo]

Sorry ive not made myself clear,

basically what i want to do is select the record using the combo box
i then have code on a button (CLOSE) that needs to move the selected record from tblmain to tblout

 

[pr2-eugin]

i then have code on a button (CLOSE) that needs to move the selected record from tblmain to tblout

Why? What is the need for this? This is where Relationships come in hand..

If the second table has many other information then link the two by means of a One to One or One to many relationship..

 

[Crabbymcdo]

the reason for this is that the first table is basically a table of Open calls, when the calls are complete i still want to keep the data as a record of work done so want the data to be moved to the tblout

 

[pr2-eugin]

Do not have to.. The data can stay on there.. Just create another column (probably Yes/No type) and if the case is close set it to True else keep it as False..

 

[Crabbymcdo]

right ok, could you possibly show me that in the document you sent me earlier, also would that stop it showing in the combo box after closure

 

[Crabbymcdo]

Please see most up to date database with full flow available to you can hopefully see what im trying to achieve

 

[Crabbymcdo]

pr2-eugin has been most helpful, but if anyone can answer my last question I would most grateful as I have no idea how to do what pr2-eugin has suggested

 

[pr2-eugin]

Crabbymcdo, so what you wanted was to Close the file right? So, let me know if this is correct..

* Open the Form 'Copy Of frmcallloggedpage', it will have a list of all 'Not Resolved/Non closed'..
* Select one of it to close using the combo,
* Click close on the 'frmdetailsclose' Form,
* The problem should not come up again..

If thats the case, I have changed the code see if that helps..

 

[Crabbymcdo]

Hi Pr2-Eugin

thanks for coming back to me, I see what you have done there, basically that may work but I wouldn't want it to go back to copy of frmcallloggedpage but to go back to frmwelcome but then when I go back to frmdetails close. also once a call has been closed I don't want it to show on the frmdetialsclose screen anymore as a record

is this possible

sorry to mess you around, and you have been so incredibly helpful so far, I really appreciate it

regards

 

[Crabbymcdo]

also is it possible so put come coding in so that when I select the record from the combo box it enter the current time into the close time for that record and not the first record in the table

 

[pr2-eugin]

Change the Form's record source to include only Unresolved.. i.e add a WHERE Condition..

WHERE Out = False

I just added a sample showing it would go to another form, you can change it to whatever form you wish to take it..

For adding time, just do the same, in the close button event..

Me.timeFieldName = Now()

 

[Crabbymcdo]

Ok, don't get it, I made the changes that you said, but now when I load the frmdetails close page it starts with a clear sheet which is perfect but then when I choose a call log from the combo box it no longer selects it.

also cannot get the time function to work