Another Dilemma!

Mar 10, 2005

I have a dropdown list called (cboClaimant) on a form which obviously allows the user to choose claimants from the list. However, if the user chooses a new claimant that is not in the list, they have the oppertunity to add to list or continue without adding, which is fine but:

If they then attempt to merge with one of the word documents that are linked to the Db, the merge fails because it cannot recognise the newly entered claimant. Hope this makes sense and that someone out there can help! here is the code that is running behind the cbobox:

Private Sub CboClaimant_NotInList(NewData As String, Response As Integer)
Dim ctl As Control
Dim stDocName As String
' Return Control object that points to combo box.
Set ctl = Me!CboClaimant
Response = acDataErrContinue
' Prompt user to verify they wish to add new value.
If MsgBox("Claimant not in list. Do you wish to add new Claimant?", vbYesNo) = vbYes Then
' Set Response argument to indicate that data is being added.

stDocName = "frmClaimant"

DoCmd.OpenForm stDocName, acNormal, , , acFormAdd
Forms![frmClaimant]![txtClaimant] = NewData
Forms![frmClaimant]![txtClaimant].SetFocus
Else
' If user chooses No undo changes.
ctl = NewData
ctl.Requery

End If
Exit_cboname_NotInList_Click:
Exit Sub
Err_cboname_NotInList_Click:
MsgBox Err.Description
Resume Exit_cboname_NotInList_Click
Me.Refresh
End Sub

View Replies


ADVERTISEMENT

Database Dilemma

Aug 8, 2005

Okay, so I'm new here. I just recently got back into database development. My first job - stepping up a department's multiple Excel spreadsheets to an Access database. Sounds pretty easy, right? Here's the problem: Out of 5 different spreadsheets, 2 of them belong to other departments and are still modified daily. So, straight linking the excel file to access is not an option. A co-worker told me that I would need to set up a front, and back-end to the database. Still, that pulls on that Excel file and doesn't allow the other department to modify it. How do you go about creating a temporary file while you are modifying the data? I don't need to put any information back into the Excel Spreadsheet. The other problem is that they want to be able to track changes that the other department makes to the spreadsheet. Is this easily accomplished? I also have to keep in mind that multiple users will be using this database. Can anyone point me in the right direction? I'm getting stressed.

View 1 Replies View Related

Table Design Dilemma

Jan 31, 2008

I need to design a database to house the following type of data (there are other fields involved to be connected to each person at each time eg tick box if attended):

Time---Person1---Person2---Person3---Person4
6:00-----John-----Peter-----Paul-----Sam
7:00 -----Mary-----Lisa -----Julie-----Mike
8:00-----etc
etc

Should I set up the the table structure data as:
time(ie 7:00, 8:00 etc)
Person(ie Person1, Person2 etc)
PersonsID
Attended (Yes/No)

and have four separate records for each time slot
and use crosstab queries (Though I want users to be able to put their own name in, and I think using Access Crosstab that you cant do this :mad:)

OR
Should I set up the table as follows:

Time(ie 7:00, 8:00 etc)
Person1
Person1Attended
Person2
Person2Attended
Person3
Person3Attended
Person4
Person4Attended

and have only one record for each timeslot.
If I did it this way how would I select and report on each individual person (would I need four separate queries?)

Thanks in anticipation
Penny

View 8 Replies View Related

Single Quote Dilemma

Aug 31, 2003

i have an error:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'Products.Catalogs LIKE '%[a-z]%' AND (Products.ItemName LIKE '%t'ai%') AND ((Products.NewItem = True) OR (Products.HotBuy = True)) ORDER BY Products.ItemName'.
/webdev/flaghouse/NEWITEM_List_Main.asp, line 149

this results when search terms contain a ' (SINGLE QUOTE). i've tried to double up all of the SINGLE QUOTES in the code and still get an error.

heres the CODE:

' check if keywords entered, split comma-delimited list into array of keywords

If Request("Keyword") > " " Then
theKeyword = Request("Keyword")
listKeywords = split(theKeyword,",")
maxCounter = ubound(listKeywords)
whereClause = "Products.ItemName LIKE '%" & listKeywords(0) & "%'"
FOR counter=1 TO maxCounter
theKeyword = listKeywords(counter)
whereClause = whereClause & " OR Products.ItemName LIKE '%" & theKeyword & "%'"
NEXT

SQLString = SQLString & " AND ("
SQLString = SQLString & whereClause & ")"
Else
theKeyword = "None"
End If


if anyone sees anything amiss, please let me know. i'm at wit's end!!

thanks. please email me at: URL. again, thank you in advance for your help.

kwc

View 5 Replies View Related

Pre-Design Dilemma - Advice Required

Apr 13, 2006

I am about to create a system where I want to use MS Access for data and MS Outlook for email,contact management etc. I have a dilemma (what do I use for storing the contacts)? I know I can use Access and use VBA to send emails etc but I also want the user to be able use Outlook directly to send emails etc as normal using that contacts (Outlook) list.
Using Office XP at the moment btw.

I would appreciate comments as to the best way forward before I start.
Thanks in advance.

View 2 Replies View Related

Sub Form Record Display Dilemma

May 26, 2005

I have a form with a sub form that opens based on a record search for a patient, whose details are displayed at the top in the main form.
The sub form displays visit information for that patient (there can be multiple visits).

On opening the sub form is filtered and only shows one record at a time. I would like it to open and show all existing visit records for the patient and be ready to enter a new visit!

View 2 Replies View Related

Option Group And Combo Box Dilemma

Jun 17, 2006

Resident Experts,
I am currently working on a database to track the employee training for my company. Access is the only tool that is available and I'm definitely a novice. I'm working on a form to enter training event information and I have an option group and combo box problem. How can I disable a combo box until a certain option is selected?

I had made a database earlier for this customer but I worked with no process and so therefore I did the best I could. However, the customer has brought something to my attention that needs correcting.

The problem is that although the current database does record training events it doesn't produce reports which show what the employee hasn't done.

Because there are training events that pertain to everyone and then there are events that only pertain to individual employees based off of a 'job code' (which identifies their specialty) I've struggled to establish a link between employees and these two different types of training.

In an effort to correct this problem, I've added a 'project mandatory code' to the 'EmployeeInfo' table which holds all employee information along with their job code. Each time a new employee is entered into the table, a default value is entered into the project mandatory row.

I have seperate tables called 'JobCodes', 'ProjectMandatoryCode', and 'TrainingEventFrequency' which are connected to my 'TrainingEventInfo' table. The TrainingEventInfo table holds the information for all known events.

When a new training event is recorded, an entry is made into my 'CompletedTrainingRecord' table. This table records the event name, date completed, and the employee clock number.

Although I think my logic is sound, I may have missed something and made no progress at all.

Having said all that, the form I am working on to enter training events into the TrainingEventInfo table is where my original question came in. The option group I have has two choices: Project Mandatory Training and Position Related Training. If the user selects Project Mandatory Training then I want the combo box to be unavailable and based off the value it will automatically input a default value for the ProjectMandatoryCode. If the user selects Position Related Training from the option group I want the combo box to become active. My combo box is run from a query which pulls the job codes from the appropriate table and this value can then be written to the record in the TrainingEventInfo table.

Because both the ProjectMandatoryCode and the JobCode are in the EmployeeInfo table I'm hopeful that I can write a query which will pull all events, related to the individual, whether they have been completed or not.

Thank you in advance for any assistance you may be able to provide. I've attached a document which may be helpful. Respectfully,
Dale Gagnon

View 3 Replies View Related

Combo Box Search In Form - Similair Value Dilemma

Dec 6, 2006

I have a combo box that searches for surnames in my database. if there are two surnames the same, it brings up the first one. if this is not what i want then how can I ask it to search again instead of just pulling down the list and searching manually?

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved