Run time error 3705 - Syntex error (Missing operator) in que

[vinwin06]

Thanks for this its really helpful.one final question to if you seen my codes in database all are interlinked and I have different validation rules for each field. My question in case if did not update previous field also the next field accepts my input. How I can prevent that .

I got an idea I can have code for in the next before update I can give another condition to check the previous field filled or not and give some mesagae box. But after giving message box the input on this field should not accept until and unless they fuelled the previous how I can do this in my form.

[HansV]

That would become very complicated. Better use the Before Update event of the form to check whether the required data have been filled in.

[vinwin06]

Yes before update event only I am including it as if previous field is null then give message box to fill up the previous field.

Until here I have code but what I want is after message box I want to restrict the user not to enter anything on the current field like it should null the value in current field and move the focus to the previous one. Is it possible to do?

[HansV]

You can't change the focus in the Before Update event of a control, so that won't work.
You could do the following instead:

In design view, set the Locked property (or even the Enabled property) to False for all controls that the user shouldn't be able to fill in before another control has been filled.

Let's say that B should only be filled in if A has already been filled in. Create an After Update event procedure for A:

Code: Select all

Private Sub A_AfterUpdate()
    If IsNull(Me.A) Then
        Me.B = Null
        Me.B.Locked = True
    Else
        Me.B.Locked = False
    End If
End Sub

You have to do this for all relevant controls, and substitute the correct names, of course.

[vinwin06]

Thanks for this it works well. For the continuity purpose I have another question if I want to upload the results into excel how I can do it. I tried using docmd.transfer spreadsheet but it does not work properly. Its not exporting all the data we have in my database .

Is there any codes available to to have this option.

[HansV]

DoCmd.TransferSpreadsheet can be used to export the records of one table (or query) to an Excel worksheet. If you want to export multiple tables (and/or queries), you'll have to use a separate DoCmd.TransferSpreadsheet command for each.

[vinwin06]

But how I can combine them into one file like in my example can u give some sample codes.

[HansV]

You can use code like this - you'll have to modify it to suit your purpose. You can call the code from the On Click event of a command button, for example.

Code: Select all

Sub Export2XL()
    ' This is the full path and name of the workbook
    Const strPath = "C:\Export\MyWorkbook.xlsx"
    Dim varTable As Variant
    ' Change and expand the array as needed
    For Each varTable In Array("Buyer", "Category", "Department")
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, _
            varTable, strPath, True
    Next varTable
    MsgBox "The tables have been exported to " & strPath, vbInformation
End Sub

[vinwin06]

Thanks for the code . Its working but it exports the tables into different sheets in the excel file. But I don't want in this way I want to export the entire master data form into excel.

[HansV]

You can't export a form to Excel (well, actually you can, but it almost certainly wouldn't be what you want). Please explain what exactly you want to export and what the result should look like.

[vinwin06]

Actually i want to export the query master data into excel. Like in the form user will fill up data once they have done with that i need to prepare some reports in excel file. so i need to export the query because master table relates to several tables data. so only query master will have the full data.
In Simple i need to export the data filled in form need to be exported all the fields available in form need to exported in excel.

[HansV]

You can use DoCmd.TransferSpreadsheet to export a query exactly the same way as a table.

[vinwin06]

I Have used the docmd codes but i am getting the result in excel but i can see the related columns as well in that excel file. like user id #, dept id # etc.
I have attached the excel file and database for your reference.

[HansV]

There is something strange about Query_Export. I'd delete the query and create it again from scratch, including only the fields that you want to export.

[vinwin06]

Thanks waiting to see the results.

[HansV]

Sorry, I meant that you yourself should delete the query and create it again.

[vinwin06]

Actually I have newly created this query only . Do u want to create one more time?

[HansV]

My apologies - I didn't investigate the query closely enough. The problem is that the Output All Fields property has been set to Yes. It should be set to No.
- Open the query in design view.
- Click in an empty part of the upper half of the query design window.
- Activate the Property Sheet (press F4 if you don't see it).
- Change the Output All Fields property from Yes to No.
- Save the query.

S0461.png

[vinwin06]

Great its works fine. thanks a lot.

[vinwin06]

Another quick question if i want to have delete records button in the form. then i need to set the referential integrity on the relationship property. Then it will works fine know?