Help With Access Form

[spike250]

Hi All,

Could someone please help me with the following two problems that are driving me round the twist.

1. I have a combo box with DLookUp code behind it so when an option is selected it populates 4 different text fields but for some reason when you select certain options it doesnt populate the text fields even though the information is in the table?

2. I am trying to have a hidden label/text box become visible when a certain option is selected in the same combo box as above. I'm not sure if this can be done?

I would appreciate any help anybody can give me with the above two problems.

Regards

Spike

 

[RuralGuy]

I doubt anyone could help you without you posting the code you are using.

 

[spike250]

Sorry About That, Here Is The Code

Private Sub cboTest_AfterUpdate()
         On Error Resume Next
cboLocation = DLookup("[lablocation]", "tbl_TestsLocations", "[testlist]='" & cboTest & "'")
cboDX = DLookup("[DXAddress]", "tbl_TestsLocations", "[testlist]='" & cboTest & "'")
cboExchange = DLookup("[DXExchange]", "tbl_TestsLocations", "[testlist]='" & cboTest & "'")
cboReceiver = DLookup("[ReceiversName]", "tbl_TestsLocations", "[testlist]='" & cboTest & "'")
Me.Refresh
       
End Sub

 

[Zigzag]

Try adding Me.

Private Sub cboTest_AfterUpdate()
On Error Resume Next
Me.cboLocation = DLookup("[lablocation]", "tbl_TestsLocations", "[testlist]='" & cboTest & "'")
Me.cboDX = DLookup("[DXAddress]", "tbl_TestsLocations", "[testlist]='" & cboTest & "'")
Me.cboExchange = DLookup("[DXExchange]", "tbl_TestsLocations", "[testlist]='" & cboTest & "'")
cboReceiver = DLookup("[ReceiversName]", "tbl_TestsLocations", "[testlist]='" & cboTest & "'")
Me.Refresh

End Sub

If cboLocation, cboDX, cboExchange, cboReceiver exist on the main and your Dlookups are good then it should work.

If not put a breakpoint at 'Me.Refresh' and hover over cboLocation, cboDX, cboExchange, cboReceiver to view the values of you Dlookups.

Garry

 

[spike250]

Hi Garry,

Thanks for the quick response.

I have added .Me as you suggested and when selecting the option that wouldnt work before still wont work now. there are about 5 or 6 out of a list of 80 that wont work.

I also tried putting in the breakpoint and hovered over the cbo.... it says the value = null.

Can you help any further?

Spike

 

[Zigzag]

If your Dlookups are returning 'null' then try the following code.

Me.cboLocation = DLookup("[lablocation]", "tbl_TestsLocations", "[testlist] like '" & cboTest & "'")
Me.cboDX = DLookup("[DXAddress]", "tbl_TestsLocations", "[testlist] Like '" & cboTest & "'")
Me.cboExchange = DLookup("[DXExchange]", "tbl_TestsLocations", "[testlist] Like '" & cboTest & "'")
cboReceiver = DLookup("[ReceiversName]", "tbl_TestsLocations", "[testlist] Like '" & cboTest & "'")
Me.Refresh

 

[spike250]

Thanks again for the quick response.

I copied and pasted the code and it still isnt working on about 5-6 options.

You can probably understand why this is driving me mad - I have gone over everything for the past month and still can't get it to work.

Thanks

Spike

 

[Zigzag]

Can you post your DB and I will take a look.

 

[spike250]

Hi,

I have never uploaded a file before but here goes.

Spike

 

[Zigzag]

Which ones don't work?

 

[spike250]

1. Borrelia (Lyme's)
2. HSV Type Specific Ab's
3. HTLV PCR on CSF's
4. JC/BK PCR (CSF's)
5. JC/BK PCR (Urine)
6. HBV DNA for eAg neg HCW's
7. Respiratory Serology (CFT's NOT Atypical)

 

[dbDamo]

2. I am trying to have a hidden label/text box become visible when a certain option is selected in the same combo box as above. I'm not sure if this can be done?

In the after update event of the combo box, something along the lines of:-

If Me.YourComboBoxNameHere = "YourValueToShowHiddenControlHere" Then
Me.YourHiddenControlNameHere.Visible = True
Else
Me.YourHiddenControlNameHere.Visible = False
End If

How are you hiding the control? I would suggest you should do it by placing the above code into the forms on current event also.

 

[Zigzag]

Spike

1. Borrelia (Lyme's)
2. HSV Type Specific Ab's
3. HTLV PCR on CSF's
4. JC/BK PCR (CSF's)
5. JC/BK PCR (Urine)
6. HBV DNA for eAg neg HCW's
7. Respiratory Serology (CFT's NOT Atypical)

If you look carefully the difference between these and the ones that work are use are you are using an inverted comma or whatever they call them '
This will mess with your Dlookup.
If you remove them it will work.

Also, looking at your sample it is not normalised. Maybe you are just feeling around to see what Access can do but it is not good tecnique.

In practice Sendaways table would only need a number to refrence the tbl_TestsLocations table and tbl_TestsLocations would need a ID field as a Primary key.

If you would like an example let me know.

 

[spike250]

Thanks for you help I will remove ' from the lists.

As I havent had much practice with Access I appologise for this. I didnt realise that this little symbol would cause so mutch trouble.

Thanks again.

Any example would be appreciated.

Spike

 

[JANR]

As I havent had much practice with Access I appologise for this. I didnt realise that this little symbol would cause so mutch trouble

To get around fields which CAN contain a ' you have to double up your quotes aruond the expression.

[FONT=Courier New]=DLookup("City", "Customers", "CompanyName = [COLOR=red]"""[/COLOR] & [CompanyName] & [COLOR=red]"""[/COLOR]")[/FONT]

JR

 

[Zigzag]

Spike

Here is an example for you to have a look at.