Replies

That’s exactly the example I gave Judy above Charlotte… She says she’s getting a run time error (429)…
Works fine for me?… What do you think is going on there?

Good Morning Judy…

You can use the Application object in the button’s On Click event…

Dim appAccess As Object
Set appAccess = CreateObject(“Access.Application.8”) ‘this is for Access ’97… I’m pretty sure you’d have to use .9 in 2000 (don’t have it here to check… )

or

Dim appAccess As Access.Application

Here’s a sample procedure I found in Access Help…

Sub DisplayForm()
Const strConPathToSamples = “C:Program ” _
& “FilesMSOfficeAccessSamples”

Dim strDB As String

‘ Initialize string to database path.
strDB = strConPathToSamples & “Northwind.mdb”
‘ Create new instance of Microsoft Access.
Set appAccess = _
CreateObject(“Access.Application.8”)
‘ Open database in Microsoft Access window.
appAccess.OpenCurrentDatabase strDB

‘ Open Orders form.
appAccess.DoCmd.OpenForm “Orders”
End Sub

HTH

Hi Dana…

I don’t know if you really want an Append Query or an Update Query… but that’s up to you to decide…
Either way, if the Start and End Date fields are of date/time data type, you can enter the following expressions into the query design:

NewStartDate: “2002” & Right(CStr(Format([StartDate],”yyyy-mm-dd”)),6)
NewEndDate: CDate([NewStartDate])+1
(substitute your own field names)

HTH

P.S. I just tried it myself… using an update query… and since both fields were date/time type in the table I had to change the expressions a touch… Try this…

NewStartDate: CDate(“2002″ & Right(CStr(Format([StartDate],”yyyy-mm-dd”)),6))
NewEndDate: CDate(“2002″ & Right(CStr(Format([StartDate],”yyyy-mm-dd”)),6))+1

When you say that you can’t see it on the screen, are you talking about looking at the relationships window?

I’m using Access 97 and I’ve never needed to create relationships in code before, but I just looked it up in help and there is a Refresh method for the Relations collection… Have you tried using that after you append “rel” to the Relations Collection?

Oops… Sorry Hans… You beat me again…

Good Morning…

I tried to get a Delete query to work for this, but no luck…
I found a way…. Maybe it’ll help, or at least give you some ideas…

I opened the table in design view and added an autonumber field and named it ID…
I saved the table and all records showed an incremented number…
I then created a make table query… grouping all fields and using Max for ID…
Running this query gave me one record for each employee in a new table… and I just deleted the old one containing the duplicates…

How does that sound?
HTH…

That’s perfect Steve! Thanks a million!!!

And sorry for the confusion… Unfortunately it’s not my setup… I was hired as a database/application developer for this company… Now THAT I can do!…
However, all of a sudden I’m in charge of automating a ton of manual work being done in Excel…
AND I’m not supposed to request changes to the data coming in or change the format of the data going out… Isn’t that always the way?!?!

In actual fact, this workbook contains 6 worksheets and numerous data ranges that need to be thrown in where needed in the final reports… I was trying to use INDEX but couldn’t quite get it… This solution is going to help me fix it all…
Anyway… Happy Friday!!… Thanks again!…

Thanks Darren…

All you have to do is add an expression to the AfterUpdate event procedure for the combobox…
Something like this:

If txtYourName “” Then
txtYourName = txtYourName & “, ” & cboYourName
Else
txtYourName = cboYourName
EndIf

I don’t know what format you’re needing the output in, but I’ve attached a quick example I made…
It just has one table and one form… but hopefully it’ll help give you ideas and help get you started…

Let me know if something doesn’t make sense…

HTH

Before I give you the wrong answer, could you clarify a couple of things?

1. Are the combo box and text box on the same form?
2. When you say “each click adds a further entry to the text box”…do you mean that it will overwrite the last entry into the text box?
   or are you creating a list of data inside the text box?

Assuming that you have the primary and foreign keys set up properly in your table design:

• Go to the relationships window
• Right click on the line joining the tables
• Select Edit Relationship
• Check “Enforce Referential Integrity” and check Cascade updates or deletes as necessary
  [/list]That will create the one to many relationships for you…
  If you get errors or if you don’t know about setting up the keys on the tables, let me know…

  HTH

Perfect Legare! Thanks so much!

Thanks a million Hans… As usual I owe you big…

Trent,

First of all… about the Search feature… see Post 250092

As for the two fields being combined into one… use the Concatenation character -> & to create a string…
(I don’t know whether you are using this in code or a query or what… This is some standard code… )

If FirstName is John and LastName is Doe, the syntax and results should look something like this:

FullName = [FirstName] & ” ” & [LastName] … That’ll return “John Doe”
or
FullName = [LastName] & “, ” & [First Name] … That’ll return “Doe, John”

HTH

Uhhhh… Wellll…. I guess I shoulda known that one…. Thanks ksnyder!

Thanks for letting us know bitram… I was watching for an answer on this one…