HELP!!!!! MS ACCESS 2000 database trouble

[Judy5305]

We have a database which was designed and created for us by someone outside of our firm. We were updating the information by doing a cleanup within it. Someone opened up MS Access and went to Tools/Database Utilities and selected Compact/Repair and then selected the database. Since then one side of the database isn't working properly. The forms (which is what we pretty much use) has several buttons on it where you can enter information on a show ( what we do) and then print the contract by clicking a button. We can no longer enter additional information here nor can we print a contract. when i try to click the information it says "You've enetered an expression that has no value" It seems as if a connection to 2 different tables have been disconnected. Can anyone help? Any information will help out I am new to access so if I can provide you with any additional information please let me know.

 

[ssatech]

Give me the field names from the (3) tables -
tblexhibitor ?ID
tblexhbooth ?ID
tblconference ?ID

Particularly, the field names ending with ___ID (e.g. BoothID)

 

[Judy5305]

They other tabs yes you can enter new data.You can see data that has been previously entered. If you start a new record you cannot enter data on this tab.

The information you requested below can you tell me where I would get this? i am not sure what info to provide you with.
Give me the field names from the (3) tables -
tblexhibitor ?ID
tblexhbooth ?ID
tblconference ?ID

Particularly, the field names ending with ___ID (e.g. BoothID)

 

[ssatech]

Judy,
Here goes! Crash course in Access Database

1) Go to the database window container and under: Objects
Tables
Queries
Forms
Reports
Macros
Modules

Go to Tables and see if you can find these (3) tables;

tblexhibitor
tblexhbooth
tblconference

Put your mouse on the table,right-click and go to design view: See if you can find a field ending with ___ID, probably with a Key sign to the left and a Data Type as Autonumber. I'm guessing these are the names of the tables.


2) Go to Queries and see if you can find a query called:
qryExhBooth - If you can go to design view and see if any of these (3) tables are there and if they are connected to each other with any of these __ID's

Give it a try

 

[Judy5305]

Ok under the table design view:

tblexhibitor
ID Autonumber
tblexhbooth
intID Number
tblconference
conID Autonumber

In the queries view (I hope I make sense here) the three tables are shown as boxes and they are linked via a line to each other with these ids Under this chart is another chart that has field table sort show criteria or. let me know if you need any info from here.

 

[ssatech]

tblexhibitor
ID Autonumber
tblexhbooth
intID Number
tblconference
conID Autonumber

Looks o.k. to me....

Remember! you said this...

Link Childs Fields...... intID
Link Master Field....... ID

And these are the correct relationship and links. But I don't know what gives...
------------------------------------------------------

Lets try something: Lets try to delete this subform and re-insert again and see what happens.

Before you do this could you possibly copy and paste the programming codes behind this Main form and reply. Open up the Main Form where these subforms reside. Go to View on menubar, click Code, Copy and Paste.

Now:

1)Make a copy of this Main Form - go to Forms and find the Name of the Form that this subform SfrmExhBooth and all the other tab forms reside. Put your mouse on the name, right-click, copy and paste - give it a different name.

2)Go to design view of this Main Form, not the copy, the original. Then delete this subform SfrmExhBooth. Put your mouse on the border of the subform, right-click and cut.

3)Go to menubar above,on the grey area menubar to the right, right-click, and click Toolbox.
-Now move your mouse over these icons until you see subform/subreport.
-Click on it and bring your mouse to where your subform SfrmExhBooth use to be.
-A subform wizard dialog box will appear.
-Check existing form
-Scroll down the list box below til you see SfrmExhBooth
-Click on it and click Next and Next and Finish.
-Now you might have to position this new subform to fit correctly like the original.
-Go to the menubar, click View, Form View.
-Test it and see if you can enter data into this new subform.

See if that gets any closer...

 

[Judy5305]

Here is the programming code: in the meatime I am going to try to reinsert the subform as you suggested.

Option Compare Database

Private Sub Check36_AfterUpdate()
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "rptExhBoothContract"
stLinkCriteria = "[BoothID]=" & Me![BoothId]

DoCmd.OpenReport stDocName, , , stLinkCriteria

End Sub

Private Sub cmdOpendfrmConfProducts_Click()
On Error GoTo Err_cmdOpendfrmConfProducts_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "dfrmConfProducts"

stLinkCriteria = "[intBoothID]=" & Me![BoothId]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdOpendfrmConfProducts_Click:
Exit Sub

Err_cmdOpendfrmConfProducts_Click:
MsgBox Err.Description
Resume Exit_cmdOpendfrmConfProducts_Click

End Sub
Private Sub cmdPrintContract_Click()
On Error GoTo Err_cmdPrintContract_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "rptExhBoothContract"
stLinkCriteria = "[BoothID]=" & Me![BoothId]

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria

Exit_cmdPrintContract_Click:
Exit Sub

Err_cmdPrintContract_Click:
MsgBox Err.Description
Resume Exit_cmdPrintContract_Click

End Sub

 

[Judy5305]

Here is the main form frmExhibitor code:

Option Compare Database

Private Sub Combo39_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[txtLName] = '" & Me![Combo39] & "'"
Me.Bookmark = rs.Bookmark
End Sub

Private Sub Combo41_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[txtLName] = '" & Me![Combo41] & "'"
Me.Bookmark = rs.Bookmark
End Sub




Private Sub txtPhone_AfterUpdate()
sfrmCompany!txtCompanyPhone = txtPhone
End Sub
Private Sub Combo58_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[txtLName] = '" & Me![Combo58] & "'"
Me.Bookmark = rs.Bookmark
End Sub

Private Sub Combo64_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[txtLName] = '" & Me![Combo64] & "'"
Me.Bookmark = rs.Bookmark
End Sub

Private Sub Combo66_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[txtLName] = '" & Me![Combo66] & "'"
Me.Bookmark = rs.Bookmark
End Sub


Private Sub cmdNewRec_Click()
On Error GoTo Err_cmdNewRec_Click

DoCmd.GoToRecord , , acNewRec

Exit_cmdNewRec_Click:
Exit Sub

Err_cmdNewRec_Click:
MsgBox Err.Description
Resume Exit_cmdNewRec_Click

End Sub
Private Sub Combo106_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[txtLName] = '" & Me![Combo106] & "'"
Me.Bookmark = rs.Bookmark
End Sub

Private Sub cmdOpenContactReport_Click()
On Error GoTo Err_cmdOpenContactReport_Click

Dim stDocName As String
Dim stLinkCriteria As String

stLinkCriteria = "[ID]=" & Me![ID]
stDocName = "rptContact"
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria

Exit_cmdOpenContactReport_Click:
Exit Sub

Err_cmdOpenContactReport_Click:
MsgBox Err.Description
Resume Exit_cmdOpenContactReport_Click

End Sub

Private Sub sfrmExhBooth_Enter()

End Sub

Private Sub txtLName_AfterUpdate()
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
End Sub

Private Sub txtReferal_Exit(Cancel As Integer)
[txtLName].SetFocus
End Sub
Private Sub Combo289_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[txtCompany] = '" & Me![Combo289] & "'"
Me.Bookmark = rs.Bookmark
End Sub

Private Sub Combo9080_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[txtCompany] = """ & Me![Combo9080] & """"
Me.Bookmark = rs.Bookmark
End Sub

Private Sub Combo9082_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[txtFName] = '" & Me![Combo9082] & "'"
Me.Bookmark = rs.Bookmark
End Sub

Private Sub Combo9084_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[txtLName] = '" & Me![Combo9084] & "'"
Me.Bookmark = rs.Bookmark
End Sub

 

[Judy5305]

Ok I tried to add the subform and I had a little bit of a problem. i could not add it in with the rest of the tabs so i just left it floating on the page. I tried to add information to it and there was no change could not add any information. It doesn't even bring up the text boxes for you to enter the information into.

 

[ssatech]

In design view of this new subform, go to properties, Data, AND is the following correct:

Link Childs Fields...... intID
Link Master Field....... ID

Otherwise,

- Give me the recordsource of the Main Form; frmExhibitor. Is it tblexhibitor? Give me the field names in this table.

- Go to the Forms container and find the subform name SfrmExhBooth. Give me the recordsource, is it tblexhbooth? or is it a query called: qryExhBooth? Give me the field names if it is a table. If it is a query, see if you can open this query in design view. If you can go to the menubar, go to View, and SQL view. Copy and Paste this Sql query in your reply.

 

[Judy5305]

In design view of this new subform, go to properties, Data, AND is the following correct:

Link Childs Fields...... intID
Link Master Field....... ID

No i changed the Link master field to this above. Still the same outlook


- Go to the Forms container and find the subform name SfrmExhBooth. Give me the recordsource, is it tblexhbooth? or is it a query called: qryExhBooth? Give me the field names if it is a table. If it is a query, see if you can open this query in design view. If you can go to the menubar, go to View, and SQL view. Copy and Paste this Sql query in your reply.

It is a query qryexhbooth. here is the sql code

SELECT tblExhibitor.booTag, tblExhibitor.txtCompany, tblExhibitor.txtFName & " " & [txtLName] AS Contact, tblExhBooths.intConId, tblConference.txtConference, tblConference.txtFormal, tblConference.txtLocation, tblConference.intYear, tblExhBooths.booCancel, tblExhBooths.booComp, tblExhBooths.BoothId, tblExhBooths.intID, tblExhBooths.intYear, tblExhBooths.txtBooth, tblExhBooths.intDepth, tblExhBooths.intWidth, [intWidth]*[intDepth] AS SqFt, IIf([booComp]=Yes,0,[curPrice-SqFt]*[SqFt]) AS Price, tblExhBooths.curBoothPrice, tblExhBooths.datBoothPaid, tblExhBooths.txtPayType, tblExhibitor.hypWeb
FROM tblConference INNER JOIN (tblExhibitor INNER JOIN tblExhBooths ON tblExhibitor.ID = tblExhBooths.intID) ON tblConference.ConID = tblExhBooths.intConId
ORDER BY tblConference.intYear DESC;

 

[ssatech]

You've enetered an expression that has no value" In the recordsource qryExhBooth of your subform: SfrmExhBooth

Can you remove this field in your query design:
Price: IIf([booComp]=Yes,0,[curPrice-SqFt]*[SqFt]) and run the Main Form: frmExhibitor. Your orginal Form; frmexhibitor with the tab subforms, not the form with re-inserted SfrmExhBooth and Open up the Main Form;frmExhibitor try to add data.

Make sure you make a copy of the SfrmExhBooth. You want to have a back up of the query: qryExhBooth and subform;SfrmExhBooth

 

[ssatech]

You've enetered an expression that has no value"
Possible expression error:
Price: IIf([booComp]=Yes,0,[curPrice-SqFt]*[SqFt])

- I don't see an expression for curPrice in your query?
?Solution:[CurBoothPrice]*[SqFt] AS curPrice
- Price: IIf([booComp]=Yes,0,[curPrice-SqFt]*[SqFt])
?Solutionrice: IIf([booComp]=Yes,0,[curPrice]-[SqFt]*[SqFt])


Copy and paste this query on the recordsource for the SfmExhBooth and see if it works:

SELECT tblExhibitor.booTag, tblExhibitor.txtCompany, tblExhibitor.txtFName & " " & [txtLName] AS Contact, tblExhBooths.intConID, tblConference.txtConference, tblConference.txtformal, tblConference.txtlocation, tblConference.intYear, tblExhBooths.booCancel, tblExhBooths.booComp, tblExhBooths.BoothID, tblExhBooths.intID, tblExhBooths.intYear, tblExhBooths.txtBooth, tblExhBooths.intDepth, tblExhBooths.intWidth, [intWidth]*[intDepth] AS SqFt, tblExhBooths.CurBoothPrice, [CurBoothPrice]*[SqFt] AS curPrice, IIf([booComp]=Yes,0,[curPrice]-[SqFt]*[SqFt]) AS Price, tblExhBooths.datBoothPaid, tblExhBooths.txtPayType, tblExhibitor.hypWeb
FROM tblExhibitor INNER JOIN (tblConference INNER JOIN tblExhBooths ON tblConference.ConID = tblExhBooths.intConID) ON tblExhibitor.ID = tblExhBooths.intID
ORDER BY tblConference.intYear DESC;

 

[Judy5305]

Ok I tried what you suggested above and there is no change.

 

[ssatech]

Ok I tried what you suggested above and there is no change."
Meaning you still cannot enter or see data in the SfmExhBooth which is in your main form frmExhibitor.

Could you open the subform:SfmExhBooth on its own and see data?

Well I don't know what to say. I re-created these tables and forms(I think) based on what you gave me - works for me.
Except the query behind the subform: SfmExhBooth gave me a little bit of a problem as it could not find:

curPrice in your expression: Price: IIf([booComp]=Yes,0,[curPrice-SqFt]*[SqFt])

nor could it calculate:,[curPrice-SqFt]*[SqFt])

 

[Judy5305]

I just pasted the data you gave me into the sfmexhbooth.

Could you open the subform:SfmExhBooth on its own and see data? Yes I can see data always could. Just cannot enter new data nor can I print a contract from here same error as on main form.