Problem with filling DatasheetForm / ContiniusForm

[Niklas Östergren]

Hi!

I would like to fill some textboxes with data, using DLookUp on the forms
Load Event. I would also like to use DataSheet form mode (subform) to
display this data (phone numbers) together with some other data which I
select from a Q.

Everything works just great if I use Single Form Mode. BUT if I use
DataSheet Form or Continius Form it fills the textboxes (DLookUp (Phone
numbers)) with data ONLY for the first person.

The DLookUp looks like this:

Me.txtPhoneHome = DLookup("[PhoneNo]", "[tblPhoneNumber]",
"[fkTypeOfPlaceID]=" & strPhoneTypeHomeID & " And [fkPersonID]=" &
intPersonID)

Should it be possible to do what I want? Any idéas?

Another Q:
Isn´t AutoKey an Integer? If I try to Dim it as an integer I get some error
(Runtime Error 13, Incompatible types).

If I Dim it as a String it workes fine!???

TIA
// Niklas

 

[John Vinson]

Hi!

I would like to fill some textboxes with data, using DLookUp on the forms
Load Event. I would also like to use DataSheet form mode (subform) to
display this data (phone numbers) together with some other data which I
select from a Q.

Ummm... why not just base the Form on the Query? Not only is DLookUp
slow and inefficient but - as you have seen - a Continuous or
Datasheet form does not lend itself well to displaying different
unbound values on multiple rows. Can't you just join tblPhoneNumber to
the query you're using for the form?

Another Q:
Isn´t AutoKey an Integer? If I try to Dim it as an integer I get some error
(Runtime Error 13, Incompatible types).

If I Dim it as a String it workes fine!???

Please post the code. What's AutoKey???

 

[Niklas Östergren]

Whell that´s what I tryed with the first time. But since I would like to use
a field (boolean) in the Q which I use to <Select> a record which I then
want to display in a single form. Or if the user whant to delete this record
or print it or what ever it´s NOT possible to join tblPhoneNumber to the Q.

Or maby it is but I don´t know a good way of doing this!

Here´s the table structure:

tblPerson.................................tblPhoneNumber....................
..........tblLookUpTypeOfPlace
PersonID (Aytokey)1------M.....fkPersonID
Name.........................................PhoneID (AutoKey)
LastName..................................fkTypeOfPlaceID M-----------1
TypeOfPlaceID(AutoKey)
...................................................PhoneNumber...............
...................TypeOfPlace(Home, Work, etc.)

In tblPhoneNumber I store phone numbers to each member. In
tblLookUpTypeOfPlace I store type of phone or place where the phone is
("Hem" = Swedich for Home, Worke, MobileNo1 etc.).

Maby Autokey is not the correct word to use but I think you understand what
I mean bu locking at the table structure above!?

Here´s the code I use:

===============================================================
Private Sub Form_Load()

Dim intPersonID As Integer ' ID number for current person
Dim strPhoneTypeHomeID As String ' Type of phonenumber to home
Dim strPhoneTypeWorkID As String ' Type of phonenumber to work
Dim strPhoneTypeMobile1ID As String ' Type of phonenumber mobile number
1
Dim strPhoneNumberHome As String ' Phone number home
Dim strPhoneNumberWork As String ' Phone number to work
Dim strPhoneNumberMobile1 As String ' Phone number to mobile cellular
phone number 1

' Get person ID
intPersonID = Me.txtPersonID


' Get phone type ID home from tblLookUpTypeOfPlace
strPhoneTypeHomeID = DLookup("[TypeOfPlaceID]", "tblLookUpTypeOfPlace",
"[TypeOfPlace]=" & Chr(34) & "Hem" & Chr(34))

' Get phone type ID to work from tblLookUpTypeOfPlace
strPhoneTypeWorkID = DLookup("[TypeOfPlaceID]", "tblLookUpTypeOfPlace",
"[TypeOfPlace]=" & Chr(34) & "Arbete" & Chr(34))

' Get phone type ID for Mobile1 from tblLookUpTypeOfPlace
strPhoneTypeMobile1ID = DLookup("[TypeOfPlaceID]", "tblLookUpTypeOfPlace",
"[TypeOfPlace]=" & Chr(34) & "MobilNr1" & Chr(34))

' Display phone numbers in textcontrol on form
If Not IsNull(intPhoneTypeHomeID) Or Not intPhoneTypeHomeID = "" Then
Me.Hem = DLookup("[PhoneNo]", "[tblPhoneNumber]", "[fkTypeOfPlaceID]=" &
strPhoneTypeHomeID & " And [fkPersonID]=" & intPersonID)
End If


If Not IsNull(intPhoneTypeWorkID) Or Not intPhoneTypeWorkID = "" Then
Me.Arbete = DLookup("[PhoneNo]", "[tblPhoneNumber]",
"[fkTypeOfPlaceID]=" & strPhoneTypeWorkID & " And [fkPersonID]=" &
intPersonID)
End If

If Not IsNull(intPhoneTypeMobile1ID) Or Not intPhoneTypeMobile1ID = "" Then
Me.Mobil1 = DLookup("[PhoneNo]", "[tblPhoneNumber]",
"[fkTypeOfPlaceID]=" & strPhoneTypeMobile1ID & " And [fkPersonID]=" &
intPersonID)
End If

End Sub
=================================================================

Why do I need the checkbox on my form to select a record?

Whell is there any other way of displying several records with the most
common data of (in my case) each memebers and still make it possible for the
user to select just one or as many as he/she wants to then be able to do
what ever he/she nedds to do (display (only one should be possible to
display at a time), print, delete)??

Any better idéa?

Whell what I mean is: Is this possible in any other way since I can´t do it
like this?

TIA!

// Niklas

 

[Niklas Östergren]

Hi!

I mean Autonumber field NOT Auto Key!

Reguards!

// Niklas