Passing variables to table

[AccessWillKill]

Hello,

I've been creating a new database and the users have asked for unbound forms to be used. This means i'm passing variables into forms using DLookup and SQL strings.

The one i'm currently on is a form which needs to check the table it would normally be referencing, see if a log exists from the number passed from a different form and then fill all the fields (naming conventions the same as that of the table) with the relevent data from the table.

I've successfuly managed to pass from form to form however from form to table has left me with a problem. i used a Dlookup a for loop and a variable to basically cycle through the forms controls exclude things like labels and then inputs the field data into the relevent field. However rather than put in the data, it is simply putting in the name of the field.

so where say Person Name = Jeff it appears as Person name = Person name on the form.

It is the variable causing the problem but i don't know why and i don't know how to fix it so it gets the field value rather than the field name.

my code is below

 For FillRecords = 0 To Forms!SubFrmAdverseEvents.Count - 1
         Debug.Print Forms!SubFrmAdverseEvents.Controls(FillRecords).Name
'prevents from filling records with labels
    If Not (TypeOf Forms!SubFrmAdverseEvents.Controls(FillRecords) Is Label) Then
        'prevents from filling records with Buttons
         If Not (TypeOf Forms!SubFrmAdverseEvents.Controls(FillRecords) Is CommandButton) Then
          If Not (TypeOf Forms!SubFrmAdverseEvents.Controls(FillRecords) Is SubForm) Then
          If Forms!SubFrmAdverseEvents.Controls(FillRecords).Visible = True Then
            'this should only leave the table data itself to fill in fields
       Debug.Print Forms!SubFrmAdverseEvents.Controls(FillRecords).Name
      
          
          AEEntityName = Forms!SubFrmAdverseEvents.Controls(FillRecords).Name
          
          Debug.Print AEEntityName
          
           Debug.Print Forms!SubFrmAdverseEvents.Controls(FillRecords).Value
                    ' finally sends the field to the relevent field on the edit form
                     Forms!SubFrmAdverseEvents.Controls(FillRecords).Value = Nz(DLookup(" '" & AEEntityName & "' ", "tblAE", " AEnumber = " & Forms!SubFrmAdverseEvents.AENumber & " "))
                     Debug.Print Forms!SubFrmAdverseEvents.Controls(FillRecords)
                     Debug.Print Forms!SubFrmAdverseEvents.AENumber.Value
End If
End If
End If
End If
'continues the loop
Next FillRecords
 Status = Not Status

 

[gemma-the-husky]

what on earth has the type of form you use bound/unbound have anything to do with the users?

how would they tell it was bound/unbound in use, anyway?

It's up to you as developer to explain to them that bound forms are more efficient, simpler to develop, and will be less expensive in the long run.

 

[AccessWillKill]

its more the people i work for thats the problem

they want more than one person to access the database at any time without causing it to be locked (i know the insanity in that comment) and basically this is the way they came up with and its up to me to come up with it in the constraints.

I suggested SQL or making a web page, which they are working on, to give people access to the database. however they're still working on it within the access.

the main problem where i work is people spend half the day trying to find who's on what database or finding someone in the IT department who can give them that answer.

I just need to know why that code is coming out with the entity name rather than the value. and how to fix it

 

[vbaInet]

they want more than one person to access the database at any time without causing it to be locked (i know the insanity in that comment) and basically this is the way they came up with and its up to me to come up with it in the constraints.

Base your forms on queriesl. Split your database - Back End in Server and each user has a copy of the Front End on their machine.

 

[gemma-the-husky]

its more the people i work for thats the problem

they want more than one person to access the database at any time without causing it to be locked (i know the insanity in that comment) and basically this is the way they came up with and its up to me to come up with it in the constraints.

I suggested SQL or making a web page, which they are working on, to give people access to the database. however they're still working on it within the access.

the main problem where i work is people spend half the day trying to find who's on what database or finding someone in the IT department who can give them that answer.

I just need to know why that code is coming out with the entity name rather than the value. and how to fix it

I still don't understand. Access is multi-user out of the box. There is no locking issue to worry about. Using bound or unbound forms makes no difference. It just makes the developer's job far harder.

I still struggle to comprehend why a user with no knowledge of Access would try and dictate how the development proceeds, or even bother. Not sure about your coroporate set-up, but someone should be able to advise.

Using SQl won't change anything, and I am pretty sure making it web-based is a non starter.

Are you in the IT dept?