combo box empty problem

[access7]

Hello

Wondered if anyone had a minute to look at my database - it is a Sales Contact Management Database to help our company keep track of any potential leads / existing clients...

The problem I have is on Frm_Company, SubFrm_Correspondence, cboContact

I have a combo box on the SubFrm_Correspondence which relates to the contacts for a particular client / prospect. I have a filter in place which limits the contents of the combo box to the contacts that belong to the current client / prospect only.
The problem I have is this... on wanting to add a new entry (or conversation) - by clicking the 'add new' command button, the sub form clears itself (this is what I want so that I can add a new entry) - the problem is that the combo box then appears empty (i.e. it is no longer showing the names of the contacts for that company)

Can anyone help me figure out why this is happening because after looking at this for far longer than I would care to admit I am stumped??

Many thanks in anticipation....


I should have perhaps also mentioned that the combo box runs off a query - I have just tried adding me.cbocontact.requery to the on click event of the 'add new' command button - this has solved the issue of the blank combo box however I now have a new problem... once I have picked a new item from the list box on the left and a new contact from the combo box mentioned for some reason the contents of the text box underneath got back to showing the first record in the table. I have an idea that this has something to do with the continuous form I am using (which is also shown in the contact history tab) but I cannot work out why this is happening??

Any help very welcome!

 

[CBrighton]

I don't have 2010 so can't look at the database, but I assume that if the combobox is showing data for the record on the form it's record source is based on controls on that form?

If so then those controls being empty will effect the results of the SQL in the rowsource.

Presumably the combobox rowsource has the company name / PK field used as criteria, in which case that field must be populated for the combobox to display the correct options.

 

[access7]

Thank you for taking the time to answer my post - I have added to it since your reply, I think I have solved the 'empty combo box error' but am now having problems with the text box (continuous form) as mentioned on above post.
I have attached a copy of the database in Access 2003 - this was from before I migrated onto 2010 - there have been a few changes but the mechanics for this problem should remain the same on both versions if you did want / have the time to have a look??

Many Thanks

 

[CBrighton]

once I have picked a new item from the list box on the left and a new contact from the combo box mentioned for some reason the contents of the text box underneath got back to showing the first record in the table. I have an idea that this has something to do with the continuous form I am using (which is also shown in the contact history tab) but I cannot work out why this is happening??

Any help very welcome!

Which textbox are you talking about, and on which form / subform.

I have tried the 2003 version adding records (and have noticed that your cancel button doesn't work in the 2003 version) but haven't seen anything else changing yet.

 

[access7]

Apologies; it is the 'Notes' text box - the sub form is called SubFrm_Correspondence and is located at the bottom of Frm_Company.
The 'add new' button should empty the contents of the form and present a blank record for the user to fill in. This code I currently have attached to the form is below... Ideally what I would like to happen with this sub form is the following;
a) I would like the form to open showing the latest entry (at the minute it shows the first record) - this is to be locked (so the user cannot edit the exisiting record)
b) On clicking of the 'add new' button I would like to form to clear so that the user can add a new entry
c) Once added and on click of the 'save' button I would like this information to be sent to the tab on the Frm_Company which is called 'Contact History'.

I hope that all makes sense; I am still very much a 'techy in training' and don't always explain things in the best way possible.... but I try

Private Sub Form_Load()
Me.CboContact.Requery
End Sub

Private Sub CmdSave_Click()
On Error GoTo Err_CmdSave_Click

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Forms!Frm_Company!SubFrm_CorrHistory.Requery

DoCmd.GoToRecord , , acNewRec

Me.CboContact.Requery

Exit_CmdSave_Click:
Exit Sub

Err_CmdSave_Click:
MsgBox Err.Description
Resume Exit_CmdSave_Click

End Sub
Private Sub cmdAdd_Click()
On Error GoTo Err_cmdAdd_Click

DoCmd.GoToRecord , , acNewRec
Me.CboContact.Requery

Exit_cmdAdd_Click:
Exit Sub

Err_cmdAdd_Click:
MsgBox Err.Description
Resume Exit_cmdAdd_Click

End Sub

 

[CBrighton]

Apologies; it is the 'Notes' text box - the sub form is called SubFrm_Correspondence and is located at the bottom of Frm_Company.
The 'add new' button should empty the contents of the form and present a blank record for the user to fill in. This code I currently have attached to the form is below... Ideally what I would like to happen with this sub form is the following;
a) I would like the form to open showing the latest entry (at the minute it shows the first record) - this is to be locked (so the user cannot edit the exisiting record)
b) On clicking of the 'add new' button I would like to form to clear so that the user can add a new entry
c) Once added and on click of the 'save' button I would like this information to be sent to the tab on the Frm_Company which is called 'Contact History'.

I hope that all makes sense; I am still very much a 'techy in training' and don't always explain things in the best way possible.... but I try

a) Change the record source of the subform from the table name to a query outputting all the fields but with the sorting which you want (whichever field has the input date I guess).

b) The 2003 database does this, clicking the add new record button clears all the fields in the subform.

c) Try adding this to the end of the save button on the subform:

Frm_Company!SubFrm_CorrHistory.Requery

 

[access7]

Thanks for your reply and suggestions... I have now based both the Frm_CorrHistory (shown on the Contact history tab of Frm_Company) and the subfrm_Correspondence (shown at the bottom of Frm_Company) on individual queries. Everything appears to be working OK now (sorting wise) however I am now getting another new problem... I always find that in fixing one thing is breaks another! ha ha
Now when I click the 'add new' button it creates a blank record (fab) and allows me to pick from the list and combo boxes (fab), however it will for some reason not allow me to type anything into the notes section (text box) - any idea why this may be?

 

[CBrighton]

No idea, and without the database (in a format which I can open) I couldn't test it.

Try opening the query / SQL from the subform which you are inputting into's recordsource to see if you can input anything into the field when it's in the query window rather than accessed via a form..

 

[access7]

hmmm, that's what is confusing me... I can edit it fine in the query, just not on the form... I am checking to see whether I have any record locks or allow edits etc that may be causing trouble? I could try and re-create this on a different machine in 2003 and send that if it helps?