After Database split, code stops working

[jcd1]

I decided to split up my database by putting the back end onto a server PC, and distribute the accde version of my database. However straight away I noticed that there has been a problem with it sort of ignoring part of the code on one of my forms.

Here is the code:

Private Sub Form_Load()

    If Not IsNull(Me.OpenArgs) Then

       'find the record that matches OpenArgs

       Dim rs As DAO.Recordset

       Set rs = Me.RecordsetClone

       rs.FindFirst "ProjectName = " & Me.OpenArgs

       If Not rs.EOF Then

          Me.Bookmark = rs.Bookmark

       End If

    End If
[B]Me.cboProjectName = [ProjectName][/B]
End Sub

Prior to the split it would work perfectly, but ever since I did it, the combo box doesn't show the Project Name of record one anymore. Similarly If I click on the drop box and pick a record, it does successfully jump to the record, but most of the time cboProjectName becomes empty.

I have tried numerous times to decompile and compact (db and vba), and it hasn't solved the problem.

I would appreciate any help.

 

[SteveH2508]

Surely you want to set the cboProjectName to Me.OpenArgs not [ProjectName] ?

 

[jcd1]

There rarely is an open argument, so that shouldn't really affect it, most of the time it would just go to the first record. I am aware that really speaking 'Me.cboProjectName = [ProjectName]' should be in the if test under else.

So I did what you suggested and changed the bottom line so it equals me.openargs, and the same problem occured.

 

[jcd1]

bump.........

 

[jcd1]

In fact, I can remove the other part as it is not really doing anything at the moment.

The only part which is not working is:

Me.cboProjectName = [ProjectName]

There is a seperate hidden textbox which shows the ProjectName of the Current record, and the idea is that that name should appear in the combo box. I changed said textbox to txtProjectName and that didn't change anything.

I can't understand how splitting the database could affect this?

 

[jdraw]

I'm not sure I'm following, but you could try

Me.cboProjectName = rs!ProjectName ???

There's an example at
http://msdn.microsoft.com/en-us/library/office/aa160845(v=office.10).aspx

 

[jcd1]

Well the code was no different even when I deleted the line -

Me.cboProjectName.Value = [ProjectName]

So that has confused me more.

Just to help your understanding a bit, I have a combo box which is used to select which record to go to on the form. This combo box has a row source of both ProjectID and ProjectName, but is bound to column 1, which is ProjectName, since ProjectID is column 0.

This worked fine when the database was not split. But since I split the database, the ProjectName no longer appears in the combobox after selection or upon load of form.

Is it something to do with the query that was created by the wizard?

 

[PeterF]

A combobox is bound to colum(0) so you need to use the projectID and not the projectName.

Me.cboProjectName = [projectID]

 

[jcd1]

The problem if I did that would be that cboProjectName would become the project number say 50 for example which means nothing to anyone, rather than the actual project name say xyz ltd. And under properties it says bound to column 1, which is actually the second column.

Please note that the form works completely fine when the database is unsplit.

 

[jdraw]

Have you tried stepping through your code for debugging purposes?
see
http://www.cpearson.com/excel/debug.htm

 

[jcd1]

I tried debugging and at the same time created a cmd button that did basically the same thing.

Private Sub Command143_Click()
Me.cboProjectName = Me.txtProjectName
End Sub

So I then I set a break point at the end sub and debugged it.

print me.cboProjectName
London School

This shows that the variable in txtProjectName is successfully being passed to the combo box, however for some strange reason the combo box just shows blank. :banghead: I don't know how this would only work when it's not split.

 

[PeterF]

You did not mention the result of my solution.
Again your combo is bound to column(0) (that's the first colum = 1), columwidth is probably set like 0,2inch (or 0;2cm depending regional settings) where the first colum is hidden and the second (column(1)) is visible.
Check the datatype of the control source (in the table) most likely it's nummeric.
Setting text to a number won't work, why it did on local tables is a miracel to me.

 

[jcd1]

You did not mention the result of my solution.
Again your combo is bound to column(0) (that's the first colum = 1), columwidth is probably set like 0,2inch (or 0;2cm depending regional settings) where the first colum is hidden and the second (column(1)) is visible.
Check the datatype of the control source (in the table) most likely it's nummeric.
Setting text to a number won't work, why it did on local tables is a miracel to me.

It doesn't have a control source as it is unbound. Also I am certain the combo box is bound to the second column, as ultimately all Im interested in is displaying the Project Name in the combo box.
But I'll give you the benefit of the doubt and try it...
...It didn't make a difference it did exactly the same thing.

What I have noticed is that for a split second the Project Name appears, but then becomes Null, so there must be something that is resetting it, or forcing it to go blank.

 

[jcd1]

Please note the Combo Box is used to navigate between records, and not change a value, thats why the control source is blank.

 

[jcd1]

Right I am halfway there...so far it works again, because I deleted the following code

Private Sub Form_Current()
Me.cboProjectName = [ProjectName]
End Sub

I also changed the bound column back to 1 as having it at 0 was coming up with "Record not found: Filtered?", which is a error handling process I have. So now when I select a record, the ProjectName successfully displays in the combo box.

Now the only issue is that upon load, the same issue occurs, which is that the record opened isn't displayed in the combo box?

 

[jcd1]

Since there was no solution to this I found a work around.

All I did was use the separate text box that shows the Project Name and put it on top of the combo box, leaving only enough space to show the arrow to activate the drop down box.

I know it's a long winded way of doing things but its what I had to do to get it to work.

Thanks anyway