Reference to main form in append query (1 Viewer)

[Lady Gragor]

Hello would somebody please help me to work out this append query?

INSERT INTO tbl_AppendTimesheetInput ( FirstName, LastName, JobNo, [Sub Job], Hours, [Hourly Charge], Amount, Division, RegoNo )

SELECT DISTINCT

'These fields are on subform 1 (This is working)
tbl_Employee.FirstName, tbl_Employee.LastName,

'These fields are on subform 2 (This is working)
qry_CurrentTimesheet.JobNo, qry_CurrentTimesheet.[Sub Job], qry_CurrentTimesheet.Hours, qry_CurrentTimesheet.[Hourly Charge], qry_CurrentTimesheet.Calculated, qry_CurrentTimesheet.Division,

'These fields are on the main form (Here is the problem)
(Forms!frm_VehicleTimeSheet!cbo_RegoNo) AS RegoNo, DateValue(Forms!frm_Vehicletimesheet!Date) AS [Date]
FROM tbl_Employee, qry_CurrentTimesheet
WHERE (((tbl_Employee.Group)=True));

It asks for parameter values for the main form. I gather this is a referencing issue but uncertain on how to resolve this.

I would really appreciate any advise as this is driving me nuts.

 

[jdraw]

Hello would somebody please help me to work out this append query?

INSERT INTO tbl_AppendTimesheetInput ( FirstName, LastName, JobNo, [Sub Job], Hours, [Hourly Charge], Amount, Division, RegoNo )

SELECT DISTINCT

'These fields are on subform 1 (This is working)
tbl_Employee.FirstName, tbl_Employee.LastName,

'These fields are on subform 2 (This is working)
qry_CurrentTimesheet.JobNo, qry_CurrentTimesheet.[Sub Job], qry_CurrentTimesheet.Hours, qry_CurrentTimesheet.[Hourly Charge], qry_CurrentTimesheet.Calculated, qry_CurrentTimesheet.Division,

'These fields are on the main form (Here is the problem)
(Forms!frm_VehicleTimeSheet!cbo_RegoNo) AS RegoNo, DateValue(Forms!frm_Vehicletimesheet!Date) AS [Date]
FROM tbl_Employee, qry_CurrentTimesheet
WHERE (((tbl_Employee.Group)=True));

It asks for parameter values for the main form. I gather this is a referencing issue but uncertain on how to resolve this.

I would really appreciate any advise as this is driving me nuts.

Is there a query or table that underlies the data on your main form? There is no reference to the data and table on the main form that I can see.

Your SQL shows only tbl_employee and qry_CurrentTimesheet .

 

[Lady Gragor]

The date is a text box on the main form and cbo_RegoNo comes from tbl_Vehicle.

 

[MSAccessRookie]

Forms!frm_VehicleTimeSheet!cbo_RegoNo) AS RegoNo,
DateValue(Forms!frm_Vehicletimesheet!Date) AS [Date]

The Expression Forms!frm_VehicleTimeSheet!cbo_RegoNo refers to a Control Field named cbo_RegoNo, that is located on a Form named frm_VehicleTimeSheet.
The Expression Forms!frm_VehicleTimeSheet!Date refers to a Control Field named Date, that is also located on a Form named frm_VehicleTimeSheet. Note that this type of control naming should be avoided as Date is a reserved word that refers to an Access Built-In Function.

Are the Control Field Names and the Form Name correct? The error would indicate that they might not be.

 

[Lady Gragor]

Hello Rookie thank you for trying to help.

I have changed the name of the date to WEDate and received a data type mismatch error. The SQL is now DateValue(Forms!frm_VehicleTimeSheet!WEDate) AS WEDate

I removed the date from the query to see if the RegoNo will work. The query runs but the RegoNo field is blank?? I'm sure this is something simple I just can't see it.

 

[Lady Gragor]

I have changed RegoNo to [Forms]![frm_VehicleTimeSheet]![cbo_RegoNo] AS RegoNo and it now works. I did the same with WEDate but get a type conversion error.

 

[Lady Gragor]

Maybe I have the form bound to the wrong table. Please advise where you think the form should be bound.

 

[MSAccessRookie]

I have changed RegoNo to [Forms]![frm_VehicleTimeSheet]![cbo_RegoNo] AS RegoNo and it now works. I did the same with WEDate but get a type conversion error.

Sorry for the delay in responding, I got a late start this morning.

A Type conversion error usually means that the program has encountered an incorrect data type in a formula. In this instance, the formula expects a date, so I suspect that the source data is not a date. Since it looks like a date, it is most likely a text field instead. Check the data type of the bound field, and the target field, and make sure they are both date/time types, since that is both what the formula expects as source and will return as a value.

To determine the correct table/query to use as a source for a data field, determine what the intended purpose of the field is, and use a source column that provides the correct data, or storage access that is required

 

[Lady Gragor]

Thank you for your reply. They are both formatted as dates. If I bind the text field, it no longer runs the buttons I have either side which change the date
Me.txt_WEDate = Me.txt_WEDate - 1

I appreciate you trying to help. I have not had such a problem like this before.

 

[Lady Gragor]

I have had to empty the tables due to company privacy but maybe someone can look at the attached please? The problem is with the date not appending to the tables

 

[MSAccessRookie]

I have had to empty the tables due to company privacy but maybe someone can look at the attached please? The problem is with the date not appending to the tables

I am experiencing some problems trying to help out. The forms are not working properly because:

• It does not appear to be possible to use the main form without any data
• At least some of the additional forms provided (to update the tables?) do not work properly.
• The data itself is not completely intuitive, and imitating it via table entry, is not working for me.

Please take time to add a few dummy records with realistic content that also show the problem that you are having so that we will be able to see it too.

-------------------------------------------------------------

I do see some issues with your queries, however. I see that most of them refer to multiple tables, and none of them join the tables on any common keys. This will create redundant and incorrect data, since each row in each table will be matched with each other row from each other table regardless of the content.

As an example, in the query qry_CurrentTimesheet, each record from tbl_Employee will be matched with each record from tblVehicle, as well as each record from tblTimesheetInput, regardless of whether or not the information applies to that employee.

As a rule, if you are using more than one table, then you need to have a common link between the tables to assist Access in matching the records that belong together, and excluding the ones that do not.

 

[Lady Gragor]

Thanks Rookie, Sorry the tables, queries etc I posted were cut and pasted out of the working database to send and didn't worry about the relationships however they were (are) set. I have just solved this, the problem was in the properties of the query. I knew I had set the table property etc to date but failed to check the query. Thank you for your help.