Bound Form

  • Thread starter Thread starter Jeremy Storm
  • Start date Start date
J

Jeremy Storm

I have been trying to set up a database and have had a
few questions. I have placed my questions in this
newsgroup and have been just amazed at the wonderful help
I have recieved. However, I have been having a lot of
trouble trying to solve this one particular problem, and
keep getting differant answers. I will just state what I
am trying to do, and see if perhaps you have any ideas.

I have 3 forms. Employee Extensions, Employee Cell Phone
Numbers, and Suppliers.

When I open the form, I would like all fields to contain
the first information in the record. I would like the
first box to allow me to type in the first few
characters, and have it auto fill then automatically
insert the rest of the information. This function does
currently work and I did this by using an unbound combo
box.

Becuase I used an unbound combo box, when I open the
form, all of the fields are entered, but my Name box (or
combo box) is empty until I put in a name. How do I make
this box contain the first name in the field.

It seems to mess things up when I make the combo box
bound because in order to allow me to type in the name
and have it auto fill, I have to keep it set to unbound
and have it allow edits... or is there another way?

Thank you for any help you may be able to provide.
 
Jeremy, can we help you distinguish between:
1. Moving to a record, and
2. Selecting a value for a foreign key field.

As an example of #1, you could place an unbound combo in the header of your
Employee form, and the combo contains the list of employees. When you select
a record in the combo, it changes the current record of the form to display
the selected employee. Since the combo is unbound, no data is changed in the
table.

As an example of #2, you need to have a relatational data structure.
tblEmployee
------------
EmployeeID AutoNumber primary key
EmployeeSurname Text
EmployeeFirstName Text
'etc

tblEmployeePhone
------------------
EmployeePhoneID Autonumber primary key
EmployeeID Number (Long) foreign key
Phone Text the phone number.

In the form for the 2nd table, you could have a combo box where the user
chooses the employee associated with a phone number. This combo is bound to
the EmployeeID field, so when you choose a value it *does* write back to the
field. If the combo has multiple columns, it may display the name (which it
is reading from tblEmployee). However, you are not changing the name of the
employee; you are simply changing which employee is associated with the
phone number.

If I have failed to answer your question, please post back information about
whether you are trying to use your combo to navigate record, or to select a
foreign key value.
 
Jeremy, can we help you distinguish between:
1. Moving to a record, and
2. Selecting a value for a foreign key field.

As an example of #1, you could place an unbound combo in the header of your
Employee form, and the combo contains the list of employees. When you select
a record in the combo, it changes the current record of the form to display
the selected employee. Since the combo is unbound, no data is changed in the
table.
Click to expand...


On that note Allen I am trying to do a simillar thing with an unbound
list box placed at the side of the form, to do #1 does it need to be
in the header of the form to work correctly?

With the unbound list box at the side of the detail section of the
form it works but I would also like to disallow edits of records
unless the user presses a command button to actuallly do that. I
found it was very easy to inadvertently change a record.

I tried disabling allow edits in the forms property but this disabled
navigation in the list box as well

Any suggestions

Darryn
-- remove two eyes to reply!
 
Hi Darryn.

You can place the navigation text box in any section of the form, but it's a
good idea to distinguish it visually from the bound controls, so the user
knows what's going on.

If you set the form's AllowEdits property to No to prevent accidential
alternations, the unbound navigation box is also uneditable. Therefore you
have to prevent editing by toggling the Locked property of the bound
controls.

A control needs to be locked if both:
a) it has a Control Source property (some don't) and
b) the control source problem is not blank.
There is actually no need to lock the controls bound to expressions (where
ControlSource starts with "="), since they are never editable anyway.

A red rectangle around the edge of the form is a good visual indicator to
the user that the record is locked.

Something like this:
----------code begins----------------
Private Sub cmdLock_Click()
Dim ctl As Control
Dim bLock As Boolean

bLock = Not Me.Surname.Locked

For Each ctl In Me.Controls
If HasProperty(ctl, "ControlSource") Then
If Len(ctl.ControlSource) > 0 And Left$(ctl.ControlSource, 1) <>
"=" Then
ctl.Locked = bLock
End If
End If
Next

Me.cmdLock.Caption = IIf(bLock, "Unlock", "Lock")
Me.rctLock.Visible = bLock
End Sub

Private Sub Form_Load()
Call cmdLock_Click
End Sub

Function HasProperty(obj As Object, strPropName As String) As Boolean
'Purpose: Return true if the object has the property.
Dim varDummy As Variant

On Error Resume Next
varDummy = obj.Properties(strPropName)
HasProperty = (Err.Number = 0)
End Function
----------code ends----------------
 
On Thu, 16 Oct 2003 18:04:26 +0800, "Allen Browne"

Hi again Allen

I see you have moved to the same isp as me!
I think they could possible learn a thing or two from bigpond about
DNS as it seems to die frequently and bigpond could learn a bit from
them about mail!!

I just had a question about the snippet of code you posted for locking
records on a form last week
Something like this:
----------code begins----------------
Private Sub cmdLock_Click()
Dim ctl As Control
Dim bLock As Boolean

bLock = Not Me.Surname.Locked
Click to expand...
What does this line refer to?
When I try to run the code I get a compile error, Method or data
member not found. I susbtituted the form name but got the same error

For Each ctl In Me.Controls
If HasProperty(ctl, "ControlSource") Then
If Len(ctl.ControlSource) > 0 And Left$(ctl.ControlSource, 1) <>
"=" Then
ctl.Locked = bLock
End If
End If
Next
Click to expand...
I understand this bit above is searching through the controls on the
form
Me.cmdLock.Caption = IIf(bLock, "Unlock", "Lock")
Me.rctLock.Visible = bLock
End Sub
Click to expand...
and this is setting the rectangle on and off,
Private Sub Form_Load()
Call cmdLock_Click
End Sub

Function HasProperty(obj As Object, strPropName As String) As Boolean
'Purpose: Return true if the object has the property.
Dim varDummy As Variant

On Error Resume Next
varDummy = obj.Properties(strPropName)
HasProperty = (Err.Number = 0)
End Function
Click to expand...
Is this supposed to be in the forms module or a general module

Sorry if these appear to be dumb questions but I am a beginner at code
and when I started the project I am doing, I did not actually realise
that so much of access is based around this. I am getting there
slowly though

Darryn
 
Hi Darryn.

iinet experienced a DDOS attack that affected their DNS server, but I assure
it is nothing like my previous ISP, where DNS would randomly fail on Win XP
machines, and ftp was out for weeks at a time. Their problems were much
worse than just the days of delay in delivering email.

Regarding the code, its goal was to cycle through the controls on a form
that were bound to fields and set their Locked property. There is no need to
set the property it if is already set correctly (since this code is called
form Form_Current also). So, the line:
bLock = Not Me.Surname.Locked
sets a boolean variable to True if the text box named Surname already has
its Locked property set. If you don't a text box named "Surname", replace
that word with one you do have.

If you would like a more complex version of the code, I can post one that
also has a recursive call to lock any subforms, and any subforms they
contain etc.
 
Hi Darryn.

iinet experienced a DDOS attack that affected their DNS server, but I assure
it is nothing like my previous ISP, where DNS would randomly fail on Win XP
machines, and ftp was out for weeks at a time. Their problems were much
worse than just the days of delay in delivering email.
Click to expand...
Fair enough, but you may find DNS tends to be a bit shaky at iinet for
some reason. I've been with them for the last three years and have
found their quality of service has dropped a bit over the last 12
months. Are you on dialup or ADSL.?
Regarding the code, its goal was to cycle through the controls on a form
that were bound to fields and set their Locked property. There is no need to
set the property it if is already set correctly (since this code is called
form Form_Current also). So, the line:
bLock = Not Me.Surname.Locked
sets a boolean variable to True if the text box named Surname already has
its Locked property set. If you don't a text box named "Surname", replace
that word with one you do have.
Click to expand...
OK I wondered if you just meant it as an example.
I dont have any already locked controls so I deleted it

To make it more simple I have used cmdlock and rctlock to test the
function

Somewhere its going all wrong for me!!

When I open the form the red rectangle is showing, but there is no
caption visible in the cmdLock button.
I can also still alter values in the controls

When I click cmdLock the rctLock disapears and the cmdLock caption
shows "Lock"

I can't work out what is going wrong, I tried commenting out the
section starting with For Each ctl in Me.Controls, to see if if it had
any effect on cmdLock and rctLock but even that bits not working
I am using Access 97 if that makes any difference
If you would like a more complex version of the code, I can post one that
also has a recursive call to lock any subforms, and any subforms they
contain etc.
Click to expand...
I need to control one subform on the form as well

Thanks for your help so far

I have quite a few things in the oncurrent event of the form may this
have nay effect on what I am trying to do? I can post all of my code
if needed.

Darryn
 
If you dirty the record programmatically, turning off the form's AllowEdits
won't take effect until the edits are saved/undone. Setting the Locked
property of the controls should still work though.

You might consider adding a
Debug.Print ctl.Name
in the loop so you can see which controls it's locking/unlocking.

Re your first question, we live on ADSL, and rely heavily on the permanent
connection. DNS was flakey again this morning.
 
Back
Top