Creating a Data Access Page 1

[RRDD]

I am trying to make a DAP for data input into a table.

I used the wizard and it has the fields I want, I can scroll through them and view the entries, but they are not editable.

I read about changing the Page DataEntry property to True, but I still cannot select any of the editboxes on the DAP and now the DAP just shows blank non-selectable editboxes.

I decided to create a dummy table to work with to test creating a DAP, my dummy table looks like this:

Group   Name    Number   Text
1       Bill    3        pink
1       Adam    5        green
2       Charlie 7        blue

I ran the wizard on the above table and again I can scroll through but not select any of the fields, and with DataEntry set to True I still cannot select/edit the textboxes.

Is there something else I need to do to be able to make an input form?


Once I get that sorted out I have a followup question: The dummy table above is where the input data goes, but on the input form I would like if possible to get the Group and Name from a different table using drop-lists. The real table has over 5000 names split into groups of about 30-50 so picking a group first would help find a name in the list which will eventually be input to the table above.

Hope that all makes sense, and thanks in advance for any help!

 

[fneily]

First - as I hope you know, the DataEntry property means your DAP opens to receive a NEW record, not display the old records. So if you want the user to see the old data, change it to False.
Second - DAP's are only UPDATABALE if you place the PRIMARY key on the DAP. Your example doesn't have a primary key so it would not be editable. So change your table structure to include a primary key and place it on the DAP.

 

[RRDD]

Thank you very much, I was not aware this required a primary key, it works ok now for scrolling through, editing, and creating new entries.

As for the second part of my question, I have a 2nd table which has a Group id and Name relationship with this table.

Here's what I want to be able to do:

1) Select a Group id by droplist from the Employee table.
2) Select a Name by droplist from the selected Group id from the Employee table.
3) Enter the additional items (as in the example table above) and save to the Data table.

I experimented with creating droplists and I can make them show Group and Name from the other table, but they are independant (name is not restricted by group) and I don't know how to get them linked together and make it save to the Data table.

Thanks again for any assistance you can provide, it is greatly appreciated.

 

[fneily]

First the easy part. Create your DAP based on the main table. Include the primary keys and all fields you want except GroupID and Name. Then create two comboboxes using the wizard. They will point to the reference table you have in your first post. So for the Group, you'll specify the above table and only show group. After the combobox is finished, bring up the property sheet, click the ALL tab and click next to the Control Source property. Click on the dropdown arrow next to it and you should see a listing of the fields the DAP is bound to. One field show say GroupID, select it. Do the same for the Name combobox. Now the selection are connected to the main table and will be saved.
The sync. part is more tricky. Remember, you're not coding in Access but Internet Explorer environment so DAP's don't work like forms. The Name combobox's source is found in the ListRowSource and ListBoundField options. So somehow the ListRowSource must be changed. I'm trying to find a simple way to do this.

 

[fneily]

Here's the hard part:

How to Synchronize Two Drop-down Lists on a Data Access Page

http://support.microsoft.com/default.aspx?scid=kb;en-us;232592

 

[RRDD]

I got the first part ok, all of the droplists now reflect data from the existing table when I scroll through the records.

Unfortunately I cannot get the synchronization to work. The DAP opens normally, but when I select anything from the Team (Group) drop-list, I get an error that says "Data provider failed while executing a provider command." with just an OK button. As soon as I click OK, I get the error "Data type mismatch in criteria expression.".

I have gone over the article you linked above 3 times with the same results. I tried it in the Northwind Sample Database and it seemed to work ok so I'm fairly certain I did the copy/pasting of the code correctly.

Do you have any suggestions? Again thanks for your help!

 

[RRDD]

I figured out the error (had put the wrong variable) and it works great!

I have one other question, can I use a drop-list to update a textbox element in a similar manner?

 

[fneily]

I'll give you that answer and something similiar - using a combobox to search for a record, you might need it in the future. The answer you need now is at the end.

Create a combobox using the wizard underneath the Header Section of the DAP. Select the combobox and then right click your mouse and select Microsoft Script Editor. On the left under Script Outline, scroll down to your combobox name and double click on it to expand the branch. Double click on "onchange". Then type the following using your field and control names: (rs.Find statement is on one line). The result is that the user will make a selection and the page will present that record.


<SCRIPT language=vbscript event=onchange for=ParcID>
Dim rs
Set rs = MSODSC.DefaultRecordset
rs.Find "[StudentID] = '" & document.all.item("ComboBoxName").value & "'", 0, 1, 1

'0 = Skip zero records before starting the search.
'1 = Search in a forward direction.
'1 = Always begin the search with the first record in the recordset.
-->
</SCRIPT>

For your immediate needs, place on the onchange event of your combobox:
Text0.Value = document.all.item("ComboBoxName").value

Change Text0 to textbox name and ComboBoxName to whatever.

 

[RRDD]

I dont want the textbox to have the same value as the droplist, I want it to look up a value based on the synchronized drop-downlist created above.

What I have is a Group name, an Employee name, and a Employee ID number. Using the previous instructions I can select a Group which presents me a list of Employee names within the group, and what I want it to do then is when I select an Employee the Employee ID is also retrieved.

I also noticed now that when I page through the records, the dropdown list for my Employee name does not populate anymore with the value from the record. It worked ok before setting up the synchronize with the Group drop-down, is there any way around this as well?

Thanks again, I appreciate all your assistance.

 

[fneily]

Ok, there's two parts - showing past data and inputting new. Go back to your design view and using the Field List button, expand your table and drag in the GroupID and Employee Name fields. This should show your previous entries. You'll only be using the dropdowns for new entries. Take out the Control Source statements on each combobox. On the onchange event for Group combobox, keep the sync code and at the bottom place
GroupID.Value = document.all.item("GroupComboBoxName").value
And for employeename combobox
EmployeeName.Value = document.all.item
("EmNameComboBoxName").value


Place on the onchange of the EmployeeName combobox:
Set rs = CreateObject("ADODB.Recordset")
sqlSelect = _
"SELECT EmployeeID FROM EmployeeTable WHERE EmployeeName = '" & EmployeeName.value & "'"
rs.Open sqlSelect, MSODSC.Connection
If rs.EOF Then
EmployeeID.value = "(Employee not found)"
Else
EmployeeID.value = rs![EmployeeID]
End If
rs.Close
Set rs = Nothing

So you should be able to select a Group that will filter the EmployeeName that will then filter the table for EmployeeID.

Haven't tested it due to some time constraints I'm on.

 

[RRDD]

I added the extra fields for paging through the records and they display ok, except now I have a droplist and a text field for each item which may end up being confusing for some of the users, but I will look at hiding/unhiding the element later on if you think that would be a viable solution?

As for the code above, I am getting a script error on this line from the Else part of the If statement ...

EmployeeID.value = rs![EmployeeID]

I verified the field names are correct, the error I get is "Error: Expected end of statement" and indicates this is at the ! character.

Thanks again, I am learning a lot from this, I haven't worked with DAP's before.

 

[genomon]

Hey RRDD!
Just FYI there is also a great Microsoft: Access Data Project forum to check out here in Tek-Tips as well!

I have great faith in fools; self-confidence my friends call it.
-Poe

 

[fneily]

You could have a label to state the combo's are for new input only, or create a copy of your DAP and have a hyperlink that says "Click here to input new data" that goes to the new DAP that's in New record mode (as you found originally), then that DAP would have the comboboxes on it. The first DAP would only show past records and block out new ones. Whatever is easier. If it works, the user doesn't care.
I was busy all day, so I'll play with it tomorrow. I probably have a typo somewhere.

 

[fneily]

I created a combobox that showed Names. I also had a textbox to show the resulting EmployeeID.
On the onchange of the combobox, place:

Dim Rst
Dim Con
Set Con = CreateObject("ADODB.Connection")
Set Rst = CreateObject("ADODB.Recordset")

Con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\test3.mdb;Jet OLEDB:System " & _
"Database=C:\Program Files\Microsoft Office\Office\System.mdw;User " & _
"ID=Admin;"

Rst.Open "SELECT EmployeeID FROM Dropdown_Table2 WHERE Name = " & _
chr(39) & Document.All.Item("ComboName").Value & chr(39), Con

Document.All.Item("Text1").Value = Rst("EmployeeID").Value
Rst.Close

DAP's must be coded using ADO. So first you must establish a connection. (CON) . You must change the "data source" path in the con statement to reflect your database.
Then change the field names and table names to yours in the SQL statement and assignment statement.
Mine worked. Good luck. Isn't this fun?

 

[PHV]

Just in case of names like O'Reilly:
Rst.Open "SELECT EmployeeID FROM Dropdown_Table2 WHERE Name = " & _
Chr(39) & [!]Replace([/!]Document.All.Item("ComboName").Value[!][tt],"'", "''")[/tt][/!] & Chr(39), Con

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[RRDD]

I have most of this working now, the only issue I am having now is some of my dropdown lists which are now linked together, previously would show the information when paging through records but do not show anything right now, I verified they are all bound properly with ControlSource.

Without adding additional fields, is there any way to populate these dropdown lists on a certain trigger event?

Once again thanks for all your assistance, it is greatly appreciated!

 

[fneily]

Their Control Source show be blank. Did you place the Group and Name textboxes on your DAP? The combo's are for new input only. On the onchange event of the combo's, you'll place the selection into the textboxes with
EmployeeName.Value = document.all.item
("EmNameComboBoxName").value (on one line)
and the same for group.

 

[RRDD]

Ah, right .. works ok now but I have one (hopefully) final question ... Is there any way to make the filtered dropdown list (from the MS article on synchronizing dropdown lists) sort the items it contains?

Thanks again!

 

[fneily]

What you could do is take the table the filtered combobox is based on, send it through a simple query to sort on the field you want. The on the second comboboxs' property, change ListRowSource to the name of that query.