Replies

Great! I had been mislead to believe they were interchangeable. Thanks for the correction.

Your code was very enlightening as well. I particularly like your commenting style. It makes for very readable code.

Should this line:

strSQL = BuildInsertSQL(ctl, NewData)

Read:

strSQL = BuildInsertSQL(ctl, strTbl, NewData)

or have I missed something again?

Glad to hear you got it all sorted out!

What is the name of the control on your report? If it is the same as the underlying field then try changing the name of the control to some thing different and then change your code accordingly.

Only other thing I can think of is what Rory suggested – the back style….my experiments with your code are working.

When you say “it doesn’t work” what results are you getting exactly?

One thing I notice is that you have quotes around some values in your if…then statements but not around others. is the value in docstatus text?

This could be done a couple of ways. I’d first try a parameter query which would reference the control values on your popup form in the criteria row or where clause of the queries on which your reports are based. Once the form is created use the expression builder to get the correct syntax for referring to the form controls in your queries. As for the date range you can use the Between…And expression in the criteria row or where clause as well.

Select * From tblWorkFlow
Where [FacilityID] = [Forms]![FromName]![FormControlName] AND [ProductionDate] Between [Forms]![FormName]![Date1ControlName] And [Forms]![FormName]![Date2ControlName]

As for opening the selected report in the click event of your “OK” button use the openform method and substitute the control name for the report name argument:

DoCmd.OpenReport Me.ControlName

Is the id field the primary key for the employee table? You should be able to use the lookup wizard in the second table to create a link between the two tables with the primary key field hidden. Here’s how the properties should look for your lookup field in the lookup tab:

Display Control=Combo Box
Row Source Type=Table/Query
Row Source=Your SQL Statement
Bound Column=2 or whichever one is the ID
Column Count=2
Column Heads=No
Column Widths=1″;0″

Default the rest

I prefer to use the enabled property of controls rather than the visible property. Since the company option is the default selection set the enabled property of the individual controls to false. Then in the after update event of the option group use an if…then or select case statement to set the properties of the appropriate controls. Something like this:

Const conCompany as Integer = 1
Const conIndividual as Integer = 2

Select Case optAddressType ‘ Name of your option group
Case conCompany
Me.txtIndividualFirstName.Enabled = False
Me.txtcompanyName.Enabled = True
Case conIndividual
Me.txtIndividualFirstName.Enabled = True
Me.txtcompanyName.Enabled = False
End Select

You will also need a similar statement in the form’s current event in which case you might want to put the const statements in the form module declarations section.

The point I was trying to make was that after you prompt the user to confirm they want to add a member to the list it is not required that you open a form since the only variable data you are storing is the vendor name, which is contained in the NewData argument. The rest is an autonumber and the date which can be handled in the Default Value property of the field in table design. Below is a modified version of your original notinlist event. Please post back with any questions.

Private Sub VendorID_NotInList(NewData As String, Response As Integer)
Dim rst As DAO.Recordset
Dim db As DAO.DATABASE
Dim strMsg as String

On Local Error GoTo ErrorHandler

strMsg = “This vendor ” & NewData & ” is not in the list. Do you want to add it?”
If MsgBox(strMsg, vbQuestion + vbYesNo, “New Vendor”)= vbYes Then
Set rst = db.OpenRecordset(“YourTableName”)
With rst
.AddNew
!VendorName = NewData
.Update
End With
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

ExitProc:
rst.Close
Set dbs = Nothing
Set rst = Nothing
Exit Sub

ErrorHandler:
Select Case Err
Case Else
strMsg = “Error Information…” & vbCrLf & vbCrLf
strMsg = strMsg & “Sub: VendorID_NotInList” & vbCrLf
strMsg = strMsg & “Description: ” & Err.Description & vbCrLf
strMsg = strMsg & “Error #: ” & Format$(Err.Number) & vbCrLf
MsgBox strMsg, vbInformation, “VendorID_NotInList”
Response = acDataErrContinue
Resume ExitProc
End Select

End Sub

You will need to open the vendor table in design mode and set the Default Value of the date field to Date(). This will set this field to the system date of the pc when a new record is added.

If you prefer to use the form make sure the form is bound to the vendor table or a query based on the vendor table.

Though I’m not as familiar with ADO I believe it is just a matter of changing the type of objects (db,rst) from DAO to ADO. One thing I left out was to destroy the objects at the end of the procedure. I’ll try and modify the post to reflect this oversight on my part.

I’ve had to import Word documents before but usually from tables in Word. The technique usually involved using the find and replace feature of Word to place some delimiting character between fields and records. Then saving the doc as text and importing into Excel or Access. You might be able to use a similar technique if your docs have a repeating structure of some kind.

If you want to just display the built in progress bar for your query go back into the startup options and check the box next to Display Status Bar.

If I understand you correctly you are storing the vendor name and date the record is created plus an autonumber for the vendorID? If this is so you really don’t need the form at all. You could open the vendor table through dao or ado and use the addnew method to create a new record setting the vendor name field to the NewData argument and the Date field to Date(). The atonumber ID field should update automatically when you use the update method. Then set the response argument to acDataErrAdded. This lets Access know a new item has been added to the combobox’s underlying recordset and to requery the list. If this isn’t happening double check the row source of your combobox. Good luck!!

The only other difference between text and memo besides the maximum number of characters that can be stored in each – text=255, memo=64k – is that you cannot index or sort a memo field. Depending on the maximum length in your description field you could try splitting it into two text fields and then concatenate them for queries and reports. This can present its own problems however.